Help with Table Normalization

0
votes

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.

2
databasedatabase-designrelational-databasenormalization
I don't know much about databases, but it looks good to me already. Your table is already BCNF and your query is very straightforward. - Owen
@Owen: The table appears to be "all key". So, 5NF. (No non-key dependencies, because there are no non-key columns.) - Mike Sherrill 'Cat Recall'
@Catcall Looking at the data it looks like User+DocType->Project, but that may be a coincidence. - Owen
@Owen: {project, doctype, user} is the only possible key for that table. - Mike Sherrill 'Cat Recall'

2 Answers

5
votes

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.

5
votes

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.