Skip to main content
154 votes
Accepted

Is it good practice to always have an autoincrement integer primary key?

It's never a bad idea to have a guaranteed unique row identifier. I guess I shouldn't say never – but let's go with the overwhelming majority of the time it's a good idea. Theoretical ...
GrandmasterB's user avatar
  • 39.4k
116 votes

Is it good practice to always have an autoincrement integer primary key?

TL;DR: Use UUID's instead of auto-increment, if you don't already have a unique way of identifying each row. I disagree with all the answers before. There are many reasons why it is a bad idea to add ...
Filip Haglund's user avatar
94 votes

Is denormalising a database for speed an anti-pattern?

Is denormalisation for performance reasons an anti-pattern? Not of itself - if something is required, then you have to find a way to do it, and it may well be better to denormalise your data than ...
Philip Kendall's user avatar
88 votes
Accepted

Is denormalising a database for speed an anti-pattern?

Performance requirements are legitimate requirements, and it's great that you have found a potential way to meet these requirements. Denormalization is a tool, not an anti-pattern. But this likely ...
amon's user avatar
  • 135k
65 votes

Is it good practice to always have an autoincrement integer primary key?

Autoincemental keys have mostly advantages. But some possible drawbacks could be: If you have a business key, you have to add a unique index on that column(s) too in order to enforce business rules. ...
Tulains Córdova's user avatar
35 votes
Accepted

Does it ever make sense NOT to condense one to one relationships?

Yes, there are tons of reasons why this may be the better design. You may have an inheritence/extension relationship, e.g. you might have a User table and then an Administrator table which has more ...
John Wu's user avatar
  • 26.6k
27 votes

Is denormalising a database for speed an anti-pattern?

Premature optimization is the root of all evil - most of it, anyway - in computer science. ~Donald Knuth Denormalizing aggregate data to avoid the aggregate function is not an anti-pattern. The anti-...
KeithS's user avatar
  • 22.2k
23 votes

Is denormalising a database for speed an anti-pattern?

Most relational databases have something called Materialized Views. It basically has the database precompute a query and keep that around for quick response. The database is then responsible for ...
dspeyer's user avatar
  • 369
22 votes

Is it good practice to always have an autoincrement integer primary key?

Just to be contrary, No, you do NOT need to always have a numeric AutoInc PK. If you analyse your data carefully you often identify natural keys in the data. This is often the case when the data has ...
mcottle's user avatar
  • 6,152
15 votes
Accepted

Database : Does it make sense to choose the zipcode as a primary key for an address?

The example is making a fundamental mistake: it's using data as a primary key. It should create and use unique IDs. The comments debate how correct it is to assume that a zipcode maps to a particular ...
candied_orange's user avatar
12 votes

Is it good practice to always have an autoincrement integer primary key?

Many tables already have a natural unique id. Do not add another unique id column (auto-increment or otherwise) onto these tables. Use the natural unique id instead. If you add another unique id, you ...
Bradley Thomas's user avatar
12 votes

Is it good practice to always have an autoincrement integer primary key?

On larger systems, ID is consistency booster, do use it almost anywhere. In this context, individual primary keys are NOT recommended, they are expensive at the bottom line (read why). Every rule has ...
miroxlav's user avatar
  • 672
12 votes
Accepted

What is the normal form of JSON?

In short JSON is a data representation according to a schema-less syntax without predefined semantics. On the opposite, normal forms are defined for abstract data model with a relational semantic ...
Christophe's user avatar
  • 78.4k
10 votes

OLTP vs OLAP - MongoDB

With NOSQL dbs you have a completely different paradigm from the way people expected to use relational databases. NOSQL is very much designed as a persistence layer for an application. So access is ...
Ewan's user avatar
  • 77k
9 votes

Applying Edgar Codd’s 1NF normalization procedure

Both need some further normalization work if you want a fully normalized model. However, it is either missing the notion of M:N relationship (first-class relationships), or it is insufficiently ...
Erik Eidt's user avatar
  • 34.2k
9 votes
Accepted

Is it ok to have 3 foreign keys in a table from one only table?

If I got this correctly, you are thinking about introducing 3 foreign key attributes MemberID1, MemberID2 and MemberID3 into AuthorizedMember because you do want to model up to 3 members which might ...
Doc Brown's user avatar
  • 209k
8 votes

Is it good practice to always have an autoincrement integer primary key?

It is not good practice to superfluous designs. I.e. - it is not good practice to always have an auto increment int primary key when one is not needed. Let's see an example where one is not needed. ...
anw's user avatar
  • 189
8 votes

Is it wasteful to create a new database table instead of using enum data type?

Option 2 with constants or enums on the programming end. Although it duplicates knowledge, violating the Single Source Of Truth principle, you can deal with it by using the Fail-fast technique. When ...
José Margaça Lopes's user avatar
8 votes

Are there existing term(s) for a 1-1 child-parent table anti-pattern?

Answer after the question was edited: There are several issues with the depicted data. The most serious is a violation of third normal form in the second table, since weekday-name and weekend-flag ...
JacquesB's user avatar
  • 59.8k
7 votes

Is it good practice to always have an autoincrement integer primary key?

An auto-incremented (identity) primary key is a good idea except to note that it is meaningless outside of the context of the database and immediate clients of that database. For example, if you ...
MatthewToday's user avatar
7 votes

Is it good practice to always have an autoincrement integer primary key?

I usually use an "identity" column (auto-incremennting integer) when defining new tables for "long-lived" data (records I expect to insert once and keep around indefinitely even if they end up "...
KeithS's user avatar
  • 22.2k
7 votes

Is it good practice to always have an autoincrement integer primary key?

Or are there scenarios where you don't want to add such a field? Sure. First of all, there are databases that have no autoincrements (e.g., Oracle, which certainly is not one of the smallest ...
AnoE's user avatar
  • 5,842
7 votes
Accepted

Should I worry about normalizing address data?

The problem with normalizing data is that you cannot be sure that you're changing it for the better, even if most cases you probably are. Also I'm assuming if you were to check for the existence of ...
Neil's user avatar
  • 22.8k
7 votes
Accepted

Thoughts on database denormalization via short-circuit key

In an RDBMS context, the first question is: why do you want to avoid the extra join? RDBMSes are designed to optimize such queries; do you experience any tangible performance issue? If it is for ...
Christophe's user avatar
  • 78.4k
6 votes

Does it ever make sense NOT to condense one to one relationships?

Adding to the excelent answer by @john-wu another, another reason is when you have A BLOB type of column like a picture. You want to have that BLOB column in a separate table, not only for queries on ...
Tulains Córdova's user avatar
6 votes

Applying Edgar Codd’s 1NF normalization procedure

You are missing a couple components in your model that represent the relationships between your artists, songs, and albums. These are sometimes referred to as associative tables. Albums have numbered ...
John Wu's user avatar
  • 26.6k
5 votes

Is it good practice to always have an autoincrement integer primary key?

As other people have made the case for an incrementing primary key I will make one for a GUID: It is guaranteed to be unique You can have one less trip to the database for data in your application. (...
Three Value Logic's user avatar
5 votes

What is the normal form of JSON?

Zeroth. First Normal Form says that data should be atomic. As in a single boolean, a single number. Even a single string is already questionable. It depends on how it is used, a string could be used ...
Jörg W Mittag's user avatar
4 votes
Accepted

Multiple items in a single column SQL

If you see someone putting different "things" into a single database table using a delimiter to keep them separate, that's a problem. It could be that someone does not have permission to change the ...
Dan Pichelman's user avatar
4 votes

Normalisation Vs Join , trade-off

Well, first of all, normalization in a relational database is not an absolute goal. There are certainly situations where maintaining a denormalized table or column does make sense from a performance ...
Robert Harvey's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible