Database design is the process of specifying the logical and/or physical parts 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.
153
votes
14answers
35k 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 ...
37
votes
9answers
2k 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 ...
37
votes
4answers
2k 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 ...
35
votes
9answers
3k views
Files - in the database or not?
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 ...
31
votes
11answers
2k 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 ...
29
votes
7answers
2k 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 ...
29
votes
5answers
1k views
Is there a name for this database structure?
Noob question, sorry if it's too basic for this exchange. 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 ...
27
votes
6answers
3k 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 ...
27
votes
7answers
690 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 ...
24
votes
5answers
2k views
Is it OK to keep a value which updates in a table?
We are developing a platform for prepaid cards, which basically holds data about cards and their balance, payments, etc.
Up until now we had a Card entity which has a collection of Account entity, ...
21
votes
5answers
2k views
What database technologies do big search engines use?
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, ...
20
votes
10answers
542 views
What is an effective way of labeling columns in a database?
I used to label columns in my databases like this:
user_id
user_name
user_password_hash
To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped ...
20
votes
6answers
1k 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 ...
19
votes
5answers
4k views
Best practices on common person fields (Name, email, address, gender etc…)
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....
19
votes
7answers
4k 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 ...
18
votes
5answers
3k views
What are some ways to implement a many-to-many relationship in a data warehouse?
The dominant topologies of Data Warehouse modelling (Star, Snowflake) are designed with one-to-many relationships in mind. Query readability, performance, and structure degrades severely when faced ...
17
votes
7answers
5k views
How do long columns impact performance and disk usage?
In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.
In reality, how much does it really matter? How ...
17
votes
1answer
2k views
Designing a platform: one database or multiple databases?
We are building a web platform that incorporates multiple services, each with its own underlying data. These services are being built independently following the principles of Service-Oriented ...
17
votes
1answer
557 views
Get and Put performance testing on Google BigTables (and other integrated DBs)
What are some effective ways to perform programmatic performance testing on database operations, especially in environments where the databases themselves do not offer dedicated tools?
For example, ...
15
votes
3answers
4k 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 ...
15
votes
2answers
18k 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 ...
15
votes
1answer
479 views
What is the reasoning behind the CAP theorem?
http://en.wikipedia.org/wiki/CAP_theorem
http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf
I think it is not very straightforward why only two of
Consistency
Availability
Partition ...
15
votes
4answers
823 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 ...
14
votes
5answers
216 views
Enforcing database integrity
Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc?
How much of a performance improvement one can expect for not ...
14
votes
7answers
1k views
What is a good way to store a large number of columns?
I have a problem deciding how to store this data in my database. Any suggestions on the best way to do it? I don't know a hell of a lot about databases, I might add.
I have data coming in formatted ...
14
votes
3answers
976 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 ...
14
votes
4answers
2k views
SQL large table design
I have a general question about SQL Server 2008 table(s) design. We currently have a table that is over 600GB and grows at about 3GB a day. This table has the appropriate indecies but is becoming a ...
14
votes
2answers
336 views
Is it more secure to go through a 3rd database to connect two databases using the same login?
We have the following setup:
Multiple production database containing private data which is used by desktop software
A web database for a public website which needs some data from the private ...
14
votes
4answers
581 views
Database design: how to handle the “archive” problem?
I'm pretty sure a lot of applications, critical applications, banks and so on do this on a daily basis.
The idea behind all that is:
all the rows must have a history
all links must stay coherent
it ...
13
votes
3answers
605 views
Duplicate column for faster queries?
The title doesn't make to much sense but i couldn't think a better title for this problem.
I have the following tabels
Projects
id
name
Customers
id
id_project
name
Payments
id
id_customer
...
13
votes
3answers
841 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 ...
13
votes
5answers
4k 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?
13
votes
6answers
2k 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 ...
12
votes
11answers
824 views
Why are constraints applied in the database rather than the code?
Why are constraint applied in Database? Will it not be more flexible to be in code?
I'm reading a beginners book on implementing databases, so this is a beginner's question!
Let's say I design a ...
12
votes
4answers
2k views
Where should you define foreign keys?
Is it better to define foreign keys in the database or in the code part of an application?
12
votes
1answer
755 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 ...
12
votes
5answers
474 views
Needing A Database Design Book
I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex ...
11
votes
4answers
351 views
Reason to not use nullable number in Oracle?
Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel ...
11
votes
6answers
3k views
Storing IP address
I have to store the IP address of all registered users in the database. I am wondering, how many characters should I declare for such a column?
Should I support IPv6 as well? If so, what is the ...
11
votes
6answers
1k views
Is polling the only way for updating app's data from a database?
An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the ...
11
votes
2answers
2k 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 ...
11
votes
5answers
8k views
Oracle Creating ER Diagrams and Data Dictionaries
I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
11
votes
4answers
463 views
How much of a security risk are published conceptual schema?
I was requesting the conceptual schemas from a government agency's information system for my research. My request has been denied on the grounds of it being a security risk.
I don't really have ...
11
votes
5answers
496 views
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?
Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:
CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle ...
11
votes
7answers
176 views
Which of these table designs is better for performance?
I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this.
Here's what I know
Company ...
11
votes
2answers
2k 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 ...
11
votes
3answers
11k views
What is the difference between MySQL VARCHAR and TEXT data types?
After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?
I was reading the list of ...
11
votes
1answer
550 views
Storing massive amounts of data from a sensor array
I have been tasked to implement a solution (app and db) to store the data samples from a huge sensor array. The array currently consists of about 20,000 sensors, but that is soon going to grow, up to ...
10
votes
4answers
3k 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 ...
10
votes
5answers
2k views
How do I force one record to have a true value for a boolean column, and all others a false value?
I want to enforce that only one record in a table is considered the "default" value for other queries or views that might access that table.
Basically, I want to guarantee that this query will always ...
