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
302 votes
15 answers
193k views

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,493
169 votes
12 answers
112k views

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
  • 39.6k
147 votes
3 answers
83k views

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,417
142 votes
8 answers
73k views

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
139 votes
3 answers
194k views

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
  • 32.6k
123 votes
3 answers
59k views

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,661
119 votes
5 answers
198k views

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,293
109 votes
5 answers
32k views

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,883
82 votes
10 answers
14k views

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

NOTE The audience of programmers.se and dba.se 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
80 votes
6 answers
75k views

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.5k
79 votes
7 answers
77k views

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.6k
74 votes
5 answers
14k views

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
70 votes
1 answer
87k views

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

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
67 votes
9 answers
43k views

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
59 votes
6 answers
42k views

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
15k views

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
57 votes
7 answers
193k views

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,378
56 votes
3 answers
48k views

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
  • 755
53 votes
2 answers
13k views

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
52k views

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
  • 6,353
51 votes
5 answers
50k views

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
34k views

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
58k views

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
47 votes
5 answers
44k views

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
46 votes
12 answers
7k views

How could DBAs be more 'programmer friendly'?

The answers and comments on the dba.se version and programmers.se 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
  • 39.6k
46 votes
9 answers
10k views

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
45 votes
5 answers
189k views

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
  • 665
44 votes
6 answers
69k views

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
  • 553
44 votes
2 answers
121k views

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
44 votes
3 answers
49k views

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
  • 842
43 votes
3 answers
23k views

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
42 votes
2 answers
55k views

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
  • 523
41 votes
5 answers
60k views

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
  • 553
41 votes
1 answer
36k views

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
41 votes
4 answers
23k views

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.6k
40 votes
3 answers
7k views

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
  • 39.6k
38 votes
3 answers
62k views

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
  • 507
38 votes
5 answers
59k views

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
  • 483
38 votes
1 answer
28k views

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
  • 779
38 votes
1 answer
66k views

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
37 votes
3 answers
10k views

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
8k views

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
7k views

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
35 votes
4 answers
47k views

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
50k views

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
34 votes
6 answers
12k views

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
33 votes
2 answers
16k views

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,019
32 votes
5 answers
19k views

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
8k views

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
32 votes
3 answers
21k views

How Does Table Partitioning Help?

I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
Rick James's user avatar
  • 1,271

1
2 3 4 5
125