CODEWITHSUNDEEP
databasedatabase-designrelational-databasenormalization
Mark
Mark

Reputation: 307

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.

Upvotes: 0

Views: 188

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562230

Normalization is not about breaking up tables for ease of maintenance. Nor is normalization about improving performance. It's about representing logical facts in a relational way, to minimize redundancy and data anomalies. If you want to learn about normalization properly, read SQL and Relational Theory by C. J. Date.

I'd stick with the single table, but add an index. Some brands of RDBMS support index-only queries, i.e. if the query can retrieve the needed columns within the index data structure, it can skip querying the base table completely. Microsoft SQL Server and MySQL are notable examples of databases that support index-only queries.

So I'd suggest creating an index over the three columns (project, doctype, user) and see if that improves your query performance.

Upvotes: 5

Jonathan Leffler
Jonathan Leffler

Reputation: 753475

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.

Upvotes: 5

Related Questions