The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
86
votes
3answers
30k 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? ...
22
votes
3answers
11k 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 ...
123
votes
12answers
58k 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 ...
11
votes
2answers
5k 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 (...
125
votes
8answers
49k 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 ...
15
votes
3answers
7k 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 ...
68
votes
5answers
12k 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 ...
28
votes
4answers
3k 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:...
12
votes
3answers
5k 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 ...
15
votes
4answers
17k 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?
16
votes
2answers
13k 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 ...
48
votes
1answer
39k 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 ...
31
votes
3answers
5k 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 (...
8
votes
2answers
15k 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 ...
7
votes
2answers
1k 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 ...
4
votes
1answer
1k 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 ...
33
votes
3answers
5k 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 '...
74
votes
10answers
11k 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 ...
38
votes
2answers
8k 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 ...
8
votes
1answer
1k 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 ...
110
votes
3answers
61k 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 ...
96
votes
5answers
26k 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 ...
18
votes
2answers
11k 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 ...
39
votes
4answers
26k 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.
9
votes
1answer
3k 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 ...
8
votes
2answers
4k 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 ...
9
votes
1answer
3k 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 ...
5
votes
5answers
958 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 ...
57
votes
7answers
53k 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 ...
28
votes
3answers
18k 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 ...
35
votes
3answers
19k 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 ...
7
votes
1answer
3k 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 ...
26
votes
2answers
7k 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 ...
26
votes
2answers
26k 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) ...
28
votes
6answers
6k 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 ...
17
votes
2answers
21k 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?
11
votes
2answers
1k 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 ...
5
votes
1answer
5k 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 ...
19
votes
2answers
35k 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 ...
8
votes
2answers
1k 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 ...
5
votes
2answers
765 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 ...
11
votes
4answers
22k 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 ...
21
votes
2answers
2k 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 ...
10
votes
3answers
12k 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 ...
5
votes
2answers
2k 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. ...
6
votes
1answer
506 views

Data inconsistency prohibition if a table refers to another via two many-to-many relationships

I have following database design (by => foreign key constraints are depicted): Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, ...
3
votes
1answer
207 views

Constrains relationship

I have the following design. Each user has it's own contacts, it's own hosts and assign those to a domain which belongs only to him. I want to insure that the host you assign to the domain belongs ...
2
votes
3answers
493 views

Database throughful design vs Quick and dirty design comparison

Take the representation of an Address, here below is a complete and very detailed implementation: here instead is a quick implementation (which more or less contains the same fields, imagine all ...
12
votes
5answers
22k 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's going to be an Android app). I have three entities: user, question and option. A question ...
14
votes
4answers
24k 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., ...