0

I am designing a database for an e-Commerce website which uses MySQL. I have made the list of tables necessary and all the fields necessary for the table. I have a total of 9 tables.

What I have done is include an auto incrementing ID as primary key on all the tables.

All my tables except 2 are normalized to 3NF. Two tables 'users' and 'outlets' are not normalized to 2NF.

Along the way I realized that normalization is cumbersome when using auto incrementing ID as a primary key. As normalization is not strictly required, I want to know if there are any downsides of using auto incrementing ID as primary key on all the tables?

dawn
  • 397
  • 5
  • 13
  • 2
    Auto incrementing ID on bridge tables (i.e. tables that are used to represent many-to-many relations) are not useful. If you have such kind of table you should avoid them. – Renzo Jun 08 '17 at 15:31
  • 1
    You can find more useful answers here, [practice for primary keys in tables](https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables) [dedicated primary key field](https://stackoverflow.com/questions/166750/should-i-have-a-dedicated-primary-key-field) [specific prefixes and autonumber](https://stackoverflow.com/questions/506164/use-item-specific-prefixes-and-autonumber-for-primary-keys) – Haswin Jun 08 '17 at 16:03
  • You can find very good answers here, [best practice for primary keys in tables](https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables) [dedicated primary key field](https://stackoverflow.com/questions/166750/should-i-have-a-dedicated-primary-key-field) – Haswin Jun 08 '17 at 16:07

5 Answers5

1

Its good to use auto incrementing ID as primary key in all the table. This will help you to auto indexing data. If you are planning to us any ORM (Doctrine2 as example) you must need primary key for each table.

Virendra Jadeja
  • 821
  • 1
  • 10
  • 20
  • Which ORM *requires* you to use an auto-incrementing key in all tables? I know some encourage it, but I don't know of any ORM that does not also allow natural keys and compound keys. That would be a pretty limiting framework. – Bill Karwin Jun 08 '17 at 15:38
  • As i mentioned in replay Doctrine2 prefers identity in each entity. When we want to generate entities from database, doctrine2 must requires identity column. – Virendra Jadeja Jun 08 '17 at 15:51
  • Doctrine 2.1 supports composite primary keys, and does not require auto-increment keys. http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html – Bill Karwin Jun 08 '17 at 16:02
1

Your question is tagged for MySQL, so I'll point out that MySQL's InnoDB storage engine uses the primary key as its clustered index for all tables.

It's more efficient to query by the clustered index when possible. But if you have an arbitrary rule that all tables must use an auto-increment primary key even if there is another column or set of columns that could serve as the primary key, and you always run queries searching by those columns and not by the auto-increment column, then you'll never gain the advantage of querying by the clustered index.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

I have created a lot of tables in my time. I have used AUTO_INCREMENT in only 1/3 of them. The rest had what seemed like a "perfectly good 'natural' PK", so I went that way.

"Normal Form" is a textbook way to get you started. In real life (in my opinion), NF later takes a back seat to performance and other considerations.

For InnoDB tables, you really should have an explicit PRIMARY KEY (either auto_inc or natural).

A generic pattern where auto_inc slows things down is a many:many mapping table, as Renzo points out, and which I discuss here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

In InnoDB, the PRIMARY KEY is stored (clustered) with the data, so the index structure (a BTree) occupies virtually no extra space. Each secondary index occupies a separate BTree that implicitly includes the PK column(s).

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

I recommend using GUID - uniqueidentifier as a Primary Key to overrule any limitations in future.

MSSQL: https://learn.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql

MySQL: https://forums.asp.net/t/1458664.aspx?UNIQUEIDENTIFIER+data+type+in+MySql

http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
-1

I agree with @Arun. Use UUIDs instead of auto incrementing INT IDs. This allows for better caching of DB writes. If you create new records they would require a new ID. If the ID is allocated by the DB then the logic layer must talk to the DB first to get the newly incremented ID. UUIDs can be generated by the logic layer or even the front end without talking to the DB. Yes, it uses more bytes but the fact that you separate your layers from the DB is a win in my books

Bugz
  • 118
  • 1
  • 9