Questions tagged [database-design]
Database design is the process of specifying the structure and thus the logical aspects of a database. The goal of database design is to make a representation of some "universe of discourse" - the types of facts, business rules and other requirements that the database is intended to model.
24,230
questions
919
votes
23
answers
719k
views
Database, Table and Column Naming Conventions? [closed]
Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:
Should table names be plural?
Should column ...
- 75k
885
votes
15
answers
518k
views
What's the difference between identifying and non-identifying relationships?
I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?
- 9,512
814
votes
10
answers
470k
views
Difference between scaling horizontally and vertically for databases [closed]
I have come across many NoSQL databases and SQL databases. There are varying parameters to measure the strength and weaknesses of these databases and scalability is one of them. What is the difference ...
- 27.8k
764
votes
6
answers
316k
views
MongoDB vs. Cassandra [closed]
I am evaluating what might be the best migration option.
Currently, I am on a sharded MySQL (horizontal partition), with most of my data stored in JSON blobs. I do not have any complex SQL queries (...
- 27.8k
566
votes
40
answers
128k
views
Database development mistakes made by application developers [closed]
What are common database development mistakes made by application developers?
495
votes
23
answers
364k
views
What is the ideal data type to use when storing latitude / longitude in a MySQL database?
Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?
- 43.8k
477
votes
12
answers
1.0m
views
Can I have multiple primary keys in a single table?
Can I have multiple primary keys in a single table?
vaithi
434
votes
4
answers
549k
views
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?
- 23.8k
431
votes
9
answers
227k
views
What are the best practices for using a GUID as a primary key, specifically regarding performance? [closed]
I have an application that uses GUID as the Primary Key in almost all tables and I have read that there are issues about performance when using GUID as Primary Key. Honestly, I haven't seen any ...
- 14.8k
422
votes
10
answers
103k
views
Is storing a delimited list in a database column really that bad?
Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.
Now, I know ...
- 18.4k
414
votes
5
answers
207k
views
Create unique constraint with null columns
I have a table with this layout:
CREATE TABLE Favorites (
FavoriteId uuid NOT NULL PRIMARY KEY,
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
MenuId uuid
);
I want to create a unique ...
- 90.2k
409
votes
17
answers
339k
views
Should each and every table have a primary key?
I'm creating a database table and I don't have a logical primary key assigned to it. Should each and every table have a primary key?
- 42k
402
votes
7
answers
372k
views
What are the lengths of Location Coordinates, latitude and longitude? [closed]
How many digits can latitude and longitude have, before the decimal, and after the decimal?
Here is an example I am getting from the location sent by a Windows Phone device:
Latitude=-63572375290155
...
- 4,045
381
votes
12
answers
185k
views
Calendar Recurring/Repeating Events - Best Storage Method
I am building a custom events system, and if you have a repeating event that looks like this:
Event A repeats every 4 days starting on March 3, 2011
or
Event B repeats every 2 weeks on Tuesday ...
- 16.6k
368
votes
4
answers
301k
views
What are OLTP and OLAP. What is the difference between them?
Actually what do they mean? All articles I find about them don't give me an idea, or my knowledge is too insufficient to understand it.
Will some one give me some resources with which I can learn ...
- 5,171
359
votes
14
answers
377k
views
Auto Generate Database Diagram MySQL [closed]
I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me ...
- 48.2k
343
votes
6
answers
142k
views
Recommended SQL database design for tags or tagging [closed]
I've heard of a few ways to implement tagging; using a mapping table between TagID and ItemID (makes sense to me, but does it scale?), adding a fixed number of possible TagID columns to ItemID (seems ...
- 44.7k
341
votes
4
answers
131k
views
What does character set and collation mean exactly?
I can read the MySQL documentation and it's pretty clear. But, how does one decide which character set to use? On what data does collation have an effect?
I'm asking for an explanation of the two and ...
- 73.6k
328
votes
7
answers
160k
views
How can you represent inheritance in a database? [closed]
I'm thinking about how to represent a complex structure in a SQL Server database.
Consider an application that needs to store details of a family of objects, which share some attributes, but have ...
- 3,485
312
votes
4
answers
180k
views
What does ON [PRIMARY] mean?
I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[...
- 3,910
298
votes
8
answers
184k
views
Max length for client ip address [duplicate]
Possible Duplicate:
Maximum length of the textual representation of an IPv6 address?
What would you recommend as the maximum size for a database column storing client ip addresses? I have it set ...
- 7,104
297
votes
4
answers
252k
views
What are database normal forms and can you give examples? [closed]
In relational database design, there is a concept of database normalization or simply normalization, which is a process of organizing columns (attributes) and tables (relations) to reduce data ...
- 27.8k
295
votes
6
answers
132k
views
Relational Database Design Patterns? [closed]
Design patterns are usually related to object oriented design.
Are there design patterns for creating and programming relational databases?
Many problems surely must have reusable solutions.
Examples ...
279
votes
38
answers
129k
views
What's wrong with foreign keys?
I remember hearing Joel Spolsky mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent ...
277
votes
3
answers
124k
views
What does principal end of an association means in 1:1 relationship in Entity framework
public class Foo
{
public string FooId{get;set;}
public Boo Boo{get;set;}
}
public class Boo
{
public string BooId{get;set;}
public Foo Foo{get;set;}
}
I was trying to do this in ...
- 4,110
276
votes
11
answers
117k
views
Schema for a multilanguage database
I'm developing a multilanguage software. As far as the application code goes, localizability is not an issue. We can use language specific resources and have all kinds of tools that work well with ...
- 4,196
273
votes
5
answers
186k
views
"Prevent saving changes that require the table to be re-created" negative effects
Preamble
I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2).
I got the error "The changes you have made require the following ...
- 2,980
263
votes
7
answers
199k
views
What's the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
What's the longest possible worldwide phone number I should consider in SQL varchar(length) for phone.
considerations:
+ for country code
() for area code
x + 6 numbers for Extension extension (so ...
- 103k
251
votes
25
answers
58k
views
Use email address as primary key?
Is email address a bad candidate for primary when compared to auto incrementing numbers?
Our web application needs the email address to be unique in the system. So, I thought of using email address ...
- 8,637
250
votes
11
answers
21k
views
First-time database design: am I overengineering? [closed]
Background
I'm a first year CS student and I work part time for my dad's small business. I don't have any experience in real world application development. I have written scripts in Python, some ...
- 4,121
239
votes
2
answers
189k
views
How big is too big for a PostgreSQL table?
I'm working on the design for a RoR project for my company, and our development team has already run into a bit of a debate about the design, specifically the database.
We have a model called Message ...
- 3,444
232
votes
10
answers
223k
views
Is there a good reason I see VARCHAR(255) used so often (as opposed to another length)?
In multiple courses, books, and jobs, I have seen text fields defined as VARCHAR(255) as kind of the default for "shortish" text. Is there any good reason that a length of 255 is chosen so often, ...
- 108k
230
votes
9
answers
189k
views
Which datatype should be used for currency?
Seems like Money type is discouraged as described here.
My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT?
- 89.5k
229
votes
8
answers
148k
views
Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
After this comment to one of my questions, I'm thinking if it is better using one database with X schemas or vice versa.
I'm developing a web application where, when people register, I create (...
- 19.1k
228
votes
5
answers
169k
views
What are best practices for multi-language database design? [closed]
What is the best way to create multi-language database? To create localized table for every table is making design and querying complex, in other case to add column for each language is simple but not ...
- 50.3k
225
votes
12
answers
124k
views
How big can a user agent string get?
If you were going to store a user agent in a database, how large would you accomdate for?
I found this technet article which recommends keeping UA under 200. It doesn't look like this is defined in ...
- 66.6k
221
votes
13
answers
515k
views
Remove Primary Key in MySQL
I have the following table schema which maps user_customers to permissions on a live MySQL database:
mysql> describe user_customer_permission;
+------------------+---------+------+-----+---------+-...
- 3,491
221
votes
15
answers
183k
views
Strings as Primary Keys in MYSQL Database [closed]
I am not very familiar with databases and the theories behind how they work. Is it any slower from a performance standpoint (inserting/updating/querying) to use Strings for Primary Keys than integers?...
- 14k
218
votes
11
answers
143k
views
Storing money in a decimal column - what precision and scale?
I'm using a decimal column to store money values on a database, and today I was wondering what precision and scale to use.
Since supposedly char columns of a fixed width are more efficient, I was ...
- 101k
210
votes
30
answers
28k
views
What should every developer know about databases? [closed]
Whether we like it or not, many if not most of us developers either regularly work with databases or may have to work with one someday. And considering the amount of misuse and abuse in the wild, and ...
207
votes
12
answers
101k
views
How to version control a record in a database [closed]
Let's say that I have a record in the database and that both admin and normal users can do updates.
Can anyone suggest a good approach/architecture on how to version control every change in this table ...
- 11.9k
196
votes
5
answers
126k
views
Relational table naming convention [closed]
I'm starting a new project and would like to get my table and column names right. For example I've always used plural in table names but recently learned singular is correct.
So if I have a table user ...
- 3,332
195
votes
19
answers
88k
views
Surrogate vs. natural/business keys [closed]
Here we go again, the old argument still arises...
Would we better have a business key as a primary key, or would we rather have a surrogate id (i.e. an SQL Server identity) with a unique constraint ...
- 11.3k
188
votes
6
answers
139k
views
What's wrong with nullable columns in composite primary keys?
ORACLE does not permit NULL values in any of the columns that comprise a primary key. It appears that the same is true of most other "enterprise-level" systems.
At the same time, most systems also ...
- 60.4k
188
votes
7
answers
111k
views
Implementing Comments and Likes in database
I'm a software developer. I love to code, but I hate databases... Currently, I'm creating a website on which a user will be allowed to mark an entity as liked (like in FB), tag it and comment.
I get ...
- 2,213
187
votes
11
answers
155k
views
How to Store Historical Data [closed]
Some co-workers and I got into a debate on the best way to store historical data. Currently, for some systems, I use a separate table to store historical data, and I keep an original table for the ...
- 7,501
184
votes
26
answers
46k
views
Is there ever a time where using a database 1:1 relationship makes sense?
I was thinking the other day on normalization, and it occurred to me, I cannot think of a time where there should be a 1:1 relationship in a database.
Name:SSN? I'd have them in the same table.
...
- 5,110
182
votes
10
answers
78k
views
How do you effectively model inheritance in a database?
What are the best practices for modeling inheritance in databases?
What are the trade-offs (e.g. queriability)?
(I'm most interested in SQL Server and .NET, but I also want to understand how other ...
- 45.2k
182
votes
24
answers
110k
views
What should I name a table that maps two tables together? [closed]
Let's say I have two tables:
Table: Color
Columns: Id, ColorName, ColorCode
Table: Shape
Columns: Id, ShapeName, VertexList
What should I call the table that maps color to shape?
Table: ???
...
- 42.1k
181
votes
11
answers
94k
views
Database Design for Tagging [closed]
How would you design a database to support the following tagging features:
items can have a large number of tags
searches for all items that are tagged with a given set of tags must be quick (the ...
- 14.4k