Search code examples
databasedatabase-designrelational-databasenormalization

Help with Table Normalization

Would really appreciate help on normalizing and optimizing this table, 'table1'. I can currently perform the following query:

SELECT user AS users
FROM table1
WHERE project='Project B'
AND doctype='DocType B'

and get what I want, but I feel it's not very efficient and would like help (with explanations) on how to improve.

Ultimately I'm trying to achieve the following:
1) Break this 1 table into multiple tables for ease of maintenance
2) Given the 'project' and 'doctype', return all users

table1:

project     doctype     user
-------     -------     ----
Project A   DocType A   User A
Project A   DocType A   User B
Project A   DocType A   User C
Project A   DocType A   User D
Project A   DocType B   User A
Project A   DocType B   User C
Project A   DocType B   User D
Project A   DocType C   User B
Project A   DocType C   User D
Project B   DocType A   User B
Project B   DocType A   User E
Project B   DocType A   User F
Project B   DocType A   User G
Project B   DocType B   User A
Project B   DocType B   User C
Project B   DocType B   User E
Project B   DocType B   User H
Project B   DocType C   User A
Project B   DocType C   User I

Please let me know if more information is needed to help. Thanks.

Solution

  • Since the table is 'all-key', and since there is no repetition, there is no obvious way to reduce the table by normalization.

    Sometimes (but not in this case) you might be able to create 3 tables with pairs of columns: PD, PU, and DU (using initial letters of the columns to form the table names). But since User A is associated with DocType A on Project A but not with DocType A on Project B, that won't work in this example.