Search code examples
database-designnormalizationdatabase-normalization

Database is normalized but resulting composite keys contain 20 columns


The problem is there are relationships which are so huge that after normalizing they have like a 20 primary keys (composite keys) which are really foreign keys.

These have to be declared as primary keys to identify the relationship uniquely. Is this correct?


Solution

  • First, do not use composite keys ever. They are a bad technique. They are slow and are a nightmare to maintain when they change.

    If you need uniqueness over two or more fields, you do not need a primary key, you need a unique index. Make the PK of the table a surrogate key (preferably int).

    If you are trying to create tables with a one-to-one relationship, it is acceptable to use the PK of the parent table as the PK of the Child table and set a PK_FK relationship between the tables; however it would be unusual to need 20 separate one-to-one tables.