Skip to main content

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
127 votes
3 answers
64k 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,721
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 ...
cubetwo1729's user avatar
145 votes
8 answers
75k 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
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:...
ypercubeᵀᴹ's user avatar
173 votes
13 answers
116k 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
  • 40.1k
74 votes
5 answers
15k 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
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
  • 40.1k
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?
ThinkingMonkey's user avatar
18 votes
3 answers
18k 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 ...
Victor's user avatar
  • 436
17 votes
2 answers
15k 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....
user5613506's user avatar
15 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 ...
Boshir's user avatar
  • 151
57 votes
3 answers
52k 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
  • 765
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 ...
Raiden Freeman's user avatar
71 votes
1 answer
93k 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
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 ...
aarnes's user avatar
  • 93
32 votes
3 answers
9k 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
9 votes
2 answers
18k 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 ...
user avatar
40 votes
3 answers
66k 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
  • 527
4 votes
1 answer
3k 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 ...
LainIwakura's user avatar
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 ...
Jakemmarsh's user avatar
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
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 ...
Snowball's user avatar
  • 327
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 ...
ebi's user avatar
  • 243
148 votes
3 answers
86k 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,447
83 votes
6 answers
81k 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.8k
14 votes
2 answers
24k 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 ...
verism's user avatar
  • 241
111 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 ...
BenV's user avatar
  • 4,903
79 votes
7 answers
78k 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.7k
54 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
29 votes
2 answers
24k 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 ...
tasmaniski's user avatar
  • 1,185
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 ...
deadcode's user avatar
  • 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 ...
Agi Hammerthief'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
19 votes
1 answer
33k 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 ...
yeln's user avatar
  • 293
43 votes
2 answers
59k 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
  • 533
36 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
29 votes
2 answers
60k 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 ...
Marcus Junius Brutus's user avatar
26 votes
2 answers
40k 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) ...
Billy ONeal's user avatar
21 votes
3 answers
19k 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 ...
Nishant's user avatar
  • 879
19 votes
3 answers
32k 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 ...
user avatar
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 ...
Vahx's user avatar
  • 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 ...
memetican's user avatar
  • 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 ...
Noam's user avatar
  • 979
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 ...
Ali's user avatar
  • 141
33 votes
2 answers
17k 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,039
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 ...
coddey's user avatar
  • 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 ...
JoeGeeky's user avatar
  • 1,267
19 votes
4 answers
34k 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., ...
KM.'s user avatar
  • 1,357
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 ...
TheSecretSquad's user avatar
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 ...
Manngo's user avatar
  • 2,849

1
2 3 4 5
12