Software Engineering Stack Exchange
Software Engineering Stack Exchange

Questions tagged [database-design]

For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.

This tag can be used by questions on database design, such as which normalization, first second, third normal form, vs. de-normalization, vs. NoSQL, learning about etc...

1318 questions
47
votes
7 answers

How can I argue convincingly against duplicating database columns?

I've started working at a new organization and one of the patterns I've been seeing in the database is duplicating fields to make writing queries easier for the business analysts. We're using Django and its ORM. In one case, we keep a…
canisrufus
  • 1,091
32
votes
9 answers

How to store record statuses (like pending, complete, draft, cancelled...)

Quite a lot of applications require records in their tables to have a status, such as 'complete', 'draft', 'cancelled'. What's the best way of storing these statuses? To illustrate what i'm getting at here is a *very short) example. I have a simple…
veganista
  • 461
29
votes
9 answers

Best way to reference static database data in code?

Many applications include 'static data': data that doesn't really change during the lifetime of the application. For example, you might have a list of Sales Areas that is likely to be a fixed list for the foreseeable future. It isn't uncommon to…
Kramii
  • 14,069
  • 5
  • 45
  • 64
16
votes
9 answers

Should I use multiple column primary keys or add a new column?

My current database design makes use of a multiple column primary key to use existing data (that would be unique anyway) instead of creating an additional column assigning each entry an arbitrary key. I know that this is allowed, but was wondering…
Covar
  • 733
  • 1
  • 4
  • 15
15
votes
8 answers

How should repetitive calendar tasks be stored in the database?

This is for a small personal project for micro-management. Basically, I store tasks in a SQLite3 database that looks like this: id INTEGER PRIMARY KEY AUTOINCREMENT label TEXT deadline INTEGER So each task has a due date (deadline) that…
François ッ Vespa ت
  • 251
14
votes
6 answers

Is it a bad practice to have a "record status" column in a database table?

I have to clarify first that the status column is not intended to reflect the status of a real-world item represented by the record (row) in the table. Rather, it is intended to show the status of the record itself. It can be as simple as…
ADTC
  • 679
12
votes
3 answers

What is exclusive arc in database and why it's evil?

I was reading most common database design mistakes made by developer Q&A on stackoverflow. At first answer there was phrase about exclusive arc: An exclusive arc is a common mistake where a table is created with two or more foreign keys where one…
Ali Arda Orhan
  • 385
11
votes
3 answers

Storing menu items with user permissions

I'm creating a menu system in PHP and MySQL. I will have several different menus and each menu will have a set of menu items connected to it. On the site, I also have different user permissions, some users can see all menu items and some items are…
span
  • 415
11
votes
3 answers

When accessing/manipulating complex data, is it better to store it in many small pieces or one large chunk?

I am building a web app that manipulates fairly complex data: guitar tabs. As a reference, guitar tabs look like…
Gabe Willard
  • 341
  • 1
  • 9
10
votes
3 answers

What to name surrogate key when logical name is already taken by the business key

What name can you give the surrogate key column in a database table when the convention suggested name collides with that of an existing user field business key? For example, if I am creating a Product table and ProductId is already used and known…
Chris Herring
  • 231
10
votes
2 answers

Database design with "draft" version of data

The database I'm currently building is pretty traditional relational database stuff, but there is one requirement that is not clear to me how model appropriately. There are many business rules which are translated into database consistency checks…
adharris
  • 203
8
votes
7 answers

Design Parts DB

I'm developing a tool that handles (electrical) parts. The parts can be created, viewed, modified, deleted, grouped and so on... In order to make this question useful for future visitors I like to keep this question universal since managing parts in…
juergen d
  • 111
8
votes
4 answers

Add an ID in every table even if it's not needed?

Is this good or bad design to add, by default, ID field in every table in a database, even when you don't see currently to usage of this ID (For instance in a MxN table) ?
Rabskatran
  • 1,421
6
votes
5 answers

database schema with unlimited hierarchical data that is modified a lot

I am trying to set up some sort of collaboration app/forum with unlimited threading; no difference between posts, threads, or forums. Any post can be answered an unlimited amount of time, and the same post can have several children, themselves…
Xananax
  • 1,390
  • 1
  • 12
  • 14
6
votes
2 answers

When to use an associative table instead of 1 foreign key?

There are plenty of examples of both associative table and one foreign key designs out there, but I can't find clear any explanation of when should each of them be preferred. For example let's assume my database needs to store the following…
potato
  • 163
  • 1
  • 5
1
2 3 4 5 6