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.
36
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 ...
38
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 ...
30
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 ...
14
votes
3answers
1k 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 ...
10
votes
2answers
2k views
How do I map an IS-A relationship into a database?
Consider the following:
entity User
{
autoincrement uid;
string(20) name;
int privilegeLevel;
}
entity DirectLoginUser
{
inherits User;
string(20) username;
string(16) ...
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 ...
9
votes
2answers
283 views
Decision criteria on when to use a non-dbo schema vs a new Database
I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out ...
31
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 ...
7
votes
3answers
481 views
Can spatial index help a “range - order by - limit” query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their ...
13
votes
1answer
791 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 ...
27
votes
6answers
4k 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 ...
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 ...
3
votes
1answer
401 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
6
votes
5answers
945 views
Survey database design: associate an answer to a user
I'm doing the conceptual model for a survey database.
The goal is store the answers given by users (it going to be an Android app).
I have three entities: user, question and option.
A question will ...
3
votes
3answers
534 views
How costly is opening and closing of a DB connection?
How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL
... when the DB software is on localhost?
... when the DB software is on another machine?
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 ...
12
votes
5answers
485 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 ...
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 ...
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?
8
votes
2answers
698 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?
...
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 ...
5
votes
5answers
2k views
Is there any good tool for making Database Design and Prototypes?
I would like to have a good tool for designing the Database Schema with all the tables, columns, data types and relations. Today I mostly do this with pen and paper but I would like to do it in a good ...
2
votes
2answers
607 views
Separate tables or partition one huge table?
I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
1
vote
2answers
1k views
How can you have two auto-incremental columns in one table?
I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to ...
6
votes
3answers
231 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
155
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 ...
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....
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 ...
6
votes
3answers
1k views
SQL Server Primary key / clustered index design decision
Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.
I've got a product history ...
14
votes
3answers
870 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 ...
8
votes
2answers
610 views
Schema-less/flexible + ACID Database?
I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be ...
6
votes
1answer
368 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
4
votes
1answer
266 views
Is that a good idea to use one database for 50.000+ shops?
I know Shopify use only one database for all shops.
But how can they handle their database with such big data?
Is that a good idea to use one database for 50.000+ shops?
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 ...
5
votes
2answers
423 views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
4
votes
5answers
3k views
Pros/Cons Using multiple databases vs using single database
Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented.
While I don't agree I'm having trouble collecting good ...
1
vote
2answers
502 views
Database normalization: Describing trait - foreign key to table, or varchar field with value?
My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order".
In my ...
0
votes
1answer
2k views
Set permissions on all objects for SQL users
I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to ...
-1
votes
2answers
512 views
help in multi-language medical database?
My project is to design a medical database for many countries.
This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language.
I started my ...
9
votes
3answers
306 views
How-to implement an entity with an unknown maximum number of attributes?
I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
8
votes
2answers
518 views
INT or CHAR for a Type Field
What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema:
create table Car
(
Name varchar(100) not null,
Description varchar(100) not ...
4
votes
3answers
792 views
How to have a one-to-many relationship with a privileged child?
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
4
votes
2answers
221 views
Introducing an 'age' category in a patients database
I want to do the following in my DB related to patients:
when patient inserts his birthday it shows his age category as: infants, children, adult, elderly... etc. to help me classify my patients
how ...
4
votes
1answer
253 views
Proper Database Partitioning
I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
3
votes
2answers
226 views
Save history editable data RDBMS
I want to make application like testing system. Every question has one or many variant of answers (and one or many can be right). I apologize that tutors and students use my testing system. It means ...
3
votes
2answers
309 views
How can I save a severely fragmented database/table?
I've recently made a discovery that might explain my slow SQL run times.
I found out that the default in SSMS is to allow mdf growth of 1MB at a time. When the size of one table alone is roughly 23 ...
1
vote
1answer
254 views
Multiple values in lookup fields
I know newer versions of MS Access allow multiple selections in lookup fields.
For example, if I'm collecting information on my students, I can have a field called Race with lookup values that ...
38
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 ...
20
votes
10answers
550 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 ...
22
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, ...
