Skip to main content

Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
303 votes
15 answers

Can MySQL reasonably perform queries on billions of rows?

I am planning on storing scans from a mass spectrometer in a MySQL database and would like to know whether storing and analyzing this amount of data is remotely feasible. I know performance varies ...
haxney's user avatar
  • 2,503
173 votes
13 answers

Should binary files be stored in the database?

What is the best place for storing binary files that are related to data in your database? Should you: Store in the database with a blob Store on the filesystem with a link in the database Store in ...
Jack Douglas's user avatar
  • 40.1k
148 votes
3 answers

Advantages and Disadvantages to using ENUM vs Integer types?

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled. Is it better for this column's data type to be an int/bool (1 or zero) or to ...
Jake Wilson's user avatar
  • 2,447
145 votes
8 answers

Why shouldn't we allow NULLs?

I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL. I don't remember why this was the case though. All I can seem to ...
Thomas Stringer's user avatar
144 votes
3 answers

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
Jonas's user avatar
  • 33.3k
127 votes
3 answers

Is a composite index also good for queries on the first field?

Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index? ...
Luciano's user avatar
  • 1,721
121 votes
5 answers

Best database and table design for billions of rows of data [closed]

I am writing an application that needs to store and analyze large amounts of electrical and temperature data. Basically I need to store large amounts of hourly electricity usage measurements for the ...
Gecata's user avatar
  • 1,313
111 votes
5 answers

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema ...
BenV's user avatar
  • 4,903
83 votes
6 answers

What is the best way to store an email address in PostgreSQL?

What would be the right datatype to store email addresses in PostgreSQL? I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
Adam Matan's user avatar
  • 11.8k
82 votes
10 answers

What are the arguments against or for putting application logic in the database layer?

NOTE The audience of and is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting ...
Phil Lello's user avatar
  • 1,469
79 votes
7 answers

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
Nick Chammas's user avatar
  • 14.7k
74 votes
5 answers

Is there a name for this database schema of key values?

We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one ...
prototype's user avatar
  • 853
71 votes
1 answer


I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go. The application currently consists of a command ...
Jim Dennis's user avatar
69 votes
9 answers

Plural vs Singular Table Name

How should I name my Tables when creating a new database? Singular: Client or Plural: Clients?
John Isaiah Carmona's user avatar
60 votes
6 answers

What problems will I get creating a database per customer?

I remember from the stackoverflow podcasts that Fog Creek use a database per customer for Fogbugz. I assume that means the Fogbugz On Demand servers have 10s of thousands of databases. We are just ...
Rik Heywood's user avatar
58 votes
9 answers

Should you design the database before the application code is written?

What is the easiest and most efficient way to design a database? From my perspective, there are a couple of options for an application's data store design: Design the database as best as you can ...
Thomas Stringer's user avatar
58 votes
7 answers

In what data type should I store an email address in database?

I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent. For example: this SQL Server recommendation ...
Thronk's user avatar
  • 1,388
57 votes
3 answers

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
bobocopy's user avatar
  • 765
54 votes
2 answers

Is it a bad practice to have several mutually exclusive one-to-one relationships?

Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc. Is there anything ...
Arthur Tarasov's user avatar
52 votes
5 answers

When should you denormalize?

I think we are all familiar with database normalization. My question is: What are some guidelines that you use when you want to denormalize the tables?
Richard's user avatar
51 votes
5 answers

How to design a database for storing a sorted list?

I am looking to store a sorted list inside a database. I want to perform the following operations efficiently. Insert(x) - Insert record x into the table Delete(x) - Delete record x from the table ...
chitti's user avatar
  • 611
50 votes
3 answers

How do I know what indexes to create for a table?

Is there a way I can figure out the best way to know which indexes to create for a table?
Nick Ginanto's user avatar
49 votes
5 answers

Can I add a unique constraint that ignores existing violations?

I have a table which currently has duplicate values in a column. I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added. Can I create a ...
Matthew's user avatar
  • 1,682
48 votes
5 answers

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
Richard Sayakanit's user avatar
48 votes
5 answers

How do I list or search all the column names in my database?

I want to search for a string in the names of the columns present in a database. I’m working on a maintenance project and some of the databases I deal with have more than 150 tables, so I'm looking ...
Webber's user avatar
  • 695
46 votes
12 answers

How could DBAs be more 'programmer friendly'?

The answers and comments on the version and version of the question "What are the arguments against or for putting application logic in the database layer?" are very revealing ...
Jack Douglas's user avatar
  • 40.1k
46 votes
9 answers

How should deletions be handled in the database?

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are ...
Abie's user avatar
  • 885
46 votes
3 answers

Is it acceptable to have circular foreign key references\How to avoid them?

Is it acceptable to have a circular reference between two tables on the foreign key field? If not, how can these situations be avoided? If so, how can data be inserted? Below is an example of ...
KidCode's user avatar
  • 862
45 votes
6 answers

Best practices on common person fields (Name, email, address, gender etc...) [closed]

What are the most common best practices on length and data type on common fields like: First Name Last Name Address Email Sex State City Country Phone Number etc....
Snow_Mac's user avatar
  • 563
44 votes
3 answers

Database Design: New Table versus New Columns

(This was suggested to be be repost here from StackOverflow) Currently have a table .. and need to start adding new data columns to it. Not every record (even going forward with new data after adding ...
cgmckeever's user avatar
44 votes
2 answers

Database model with users, roles and rights

I have a database model with a user table and role table. I want to control the access (rights) to up to 10 different elements. The access can be granted to either a role or a single user. Below is ...
taudorf's user avatar
  • 543
43 votes
2 answers

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
Sonique's user avatar
  • 533
42 votes
5 answers

Why does a UNIQUE constraint allow only one NULL in SQL Server?

In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should ...
user87166's user avatar
  • 563
42 votes
4 answers

Does the order of columns in a table's definition matter?

When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an ...
Nick Chammas's user avatar
  • 14.7k
41 votes
1 answer

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references ...
stUrb's user avatar
  • 747
40 votes
3 answers

Are composite primary keys bad practice?

I want to know if composite primary keys are bad practice and if not, in which scenarios is their use beneficial? My question is based on this article Note the part about composite primary keys: Bad ...
hackvan's user avatar
  • 527
40 votes
3 answers

Should every table have a single-field surrogate/artificial primary key?

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are '...
Jack Douglas's user avatar
  • 40.1k
39 votes
5 answers

In SQL, is it composite or compound keys?

About SQL (Computing/Databases): When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys? I've ...
igordcard's user avatar
  • 493
39 votes
1 answer

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
Juan Carlos Coto's user avatar
38 votes
1 answer

JSONB with indexing vs. hstore

I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage. As a first step, understanding that JOINS are expensive, ...
ahron's user avatar
  • 813
38 votes
2 answers

Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
theGtknerd's user avatar
37 votes
3 answers

Duplicate column for faster queries?

The title doesn't make too much sense, but I couldn't think a better title for this problem. I have the following tables Projects id name Customers id id_project name Payments id id_customer ...
Gabriel Solomon's user avatar
36 votes
5 answers

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of a problem since I live and work in America (where the only language that exists is "American". ahem) After ...
RLH's user avatar
  • 927
36 votes
3 answers

At what point does one database per client become unfeasible?

For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system. However, we're developing a new system that ...
NibblyPig's user avatar
  • 661
36 votes
6 answers

Arbitrarily ordering records in a table

A common need when using a database is to access records in order. For example, if I have a blog, I want to be able to reorder my blog posts in arbitrary order. These entries often have lots of ...
Tom Marthenal's user avatar
35 votes
4 answers

Double entry bookkeeping database design

I'm creating accounting software. I need to enforce double entry bookkeeping. I have the classical problem of one row per transaction versus two rows. Let's take an example and see how it would be ...
Dmitry Kudryavtsev's user avatar
34 votes
3 answers

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
user avatar
33 votes
2 answers

Custom unique constraint, only enforced if one column has a specific value

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is 'true', then I want the ...
Faheem Mitha's user avatar
  • 1,039
32 votes
5 answers

What database technologies do big search engines use? [closed]

Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data? What sort of database or technologies do they employ for this? It takes few milliseconds, ...
rkosegi's user avatar
  • 518
32 votes
3 answers

Is using multiple foreign keys separated by commas wrong, and if so, why?

There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called DealCategories with the following structure: DealCategoryId (...
Sarawut Positwinyu's user avatar

2 3 4 5