Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
566
questions
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?
...
- 1,661
27
votes
3
answers
14k
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 ...
- 881
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 ...
- 42.1k
29
votes
4
answers
6k
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 frequencies:...
- 97.4k
170
votes
12
answers
113k
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 ...
- 39.7k
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 ...
- 853
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 '...
- 39.7k
18
votes
4
answers
25k
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?
- 605
18
votes
3
answers
17k
views
Implementing versioning system with MySQL
I know this has been asked here and here, but I have the same idea with a different possible implementation and I need some help.
Initially I had my blogstories table with this structure:
| Column ...
- 436
17
votes
2
answers
14k
views
How to model an entity type that can have different sets of attributes?
I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items.
This is pretty straightforward, yes; however, each Item belongs to a certain Category (e....
- 295
14
votes
3
answers
9k
views
Modeling a scenario in which each Music Artist is either a Group or a Solo Performer
I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail below.
Scenario description
An Artist has a Name, and ...
- 141
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 ...
- 755
17
votes
2
answers
19k
views
Many to Many and Weak Entities
I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship.
Example: An artist has an album (the album cannot exist ...
- 171
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 ...
- 755
9
votes
2
answers
6k
views
Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money
In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred
from member to member,
from member to organization,
from organization ...
- 93
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 (...
9
votes
2
answers
17k
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 ...
user7970
4
votes
1
answer
2k
views
Storing created/retired dates for a large amount of different entities
In the db I'm working on now almost every entity has these 4 columns:
CreatedDate
CreatedBy
RetiredDate
RetiredBy
Usually this is used for logging purposes, and for some of the entities the ...
- 173
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 ...
- 507
8
votes
1
answer
3k
views
Database schema for entities with two possible owner/parent types?
I'm using PostgreSQL with Sequelize as my ORM.
I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
- 183
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 ...
- 1,469
15
votes
1
answer
8k
views
Enforcing constraints "two tables away"
I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst
where "inst" is short for ...
- 327
147
votes
3
answers
84k
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 ...
- 2,417
80
votes
6
answers
76k
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.
- 11.5k
14
votes
2
answers
23k
views
Modelling a database structure for multiple user types and their contact information
I'm designing a database that will store users of varying types. Predominantly (but not exclusively) they will be Actors, Directors and Writers. Currently there are just four user types that are of ...
- 241
14
votes
1
answer
7k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 243
109
votes
5
answers
33k
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 ...
- 4,883
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 ...
- 14.6k
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 ...
- 801
28
votes
2
answers
22k
views
Ready-to-Use Database models example
Where can I find ready-to-use database models ?
I don't need a database with data in it, but only schemas (UML diagrams). Perhaps something like the data models at this link, but much more complex and ...
- 1,155
14
votes
2
answers
3k
views
Best data modelling approach to handle redundant foreign keys in a database about Surveys, Questions and Responses
I am looking for advice on the best relational modelling approach to store surveys, questions, and responses.
I am looking for which of the two approaches below looks best, or an alternative approach ...
- 295
5
votes
5
answers
3k
views
How do I prove that the act of removing foreign keys doesn't corrupt existing data?
TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
- 249
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 ...
- 1,271
19
votes
1
answer
30k
views
Designing a friendships database structure: Should I use a multivalued column?
Say I have a table called User_FriendList, which has the following characteristics:
CREATE TABLE User_FriendList (
ID ...,
User_ID...,
FriendList_IDs...,
CONSTRAINT User_Friendlist_PK ...
- 293
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?
- 523
35
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 ...
- 453
29
votes
2
answers
57k
views
Disable all constraints and table checks while restoring a dump
I've obtained a dump of my PostgreSQL database with:
pg_dump -U user-name -d db-name -f dumpfile
which I then proceed to restore in another database with:
psql X -U postgres -d db-name-b -f ...
- 3,279
26
votes
2
answers
39k
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) ...
- 571
20
votes
3
answers
18k
views
What are the best practices regarding lookup tables in relational databases?
Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column.
For example, suppose we have a lookup table called ...
- 849
19
votes
3
answers
31k
views
Index max row size error
Is there a upper bound for an array column?
I am getting this error when inserting into the array field -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
Here's my ...
user310525
9
votes
2
answers
2k
views
Advice on a basic design, first time database design
I am taking a course to become a Java developer.
The course involves using databases but we never really design any, unfortunately.
Most of the time we get pre-made databases and we have to ...
- 225
8
votes
2
answers
3k
views
Features and Patterns for Managing Ordered Lists
I have a common scenario in app design, where I need to store an ordered list, and make the ordering easily accessible and adjustable via an application UI.
The ordering operations are simple and ...
- 183
5
votes
1
answer
6k
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 ...
- 969
4
votes
3
answers
16k
views
How to build a table for a private messaging system that supports replies?
I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply ...
- 141
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 ...
- 1,019
28
votes
2
answers
9k
views
Handling growing number of Tenants in Multi-tenant Database Architecture
Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way ...
- 481
24
votes
2
answers
3k
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 ...
- 1,257
19
votes
4
answers
33k
views
One Big Database vs. Several Smaller Ones
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,
...
- 1,317
14
votes
4
answers
27k
views
Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping
I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored ...
- 601
11
votes
5
answers
2k
views
First Normal Form: Definitive Definition
I am trying to get a definitive version of what is First Normal Form. Everything I read has a slightly different spin.
Many authorities, such as Date, say that by definition a relation is always in ...
- 2,779