Normalizing a simple SQL Table

Question

I have two different tables and I am not sure of the best way to get it out of the first normal form and into the second normal form. The first table hold the user information while the second is the products associated with the account. If I do it this way, I know it is only in the NF1 and that the foreign key of User_ID will be repeated many times in Table 2. See the tables below.

Table 1
|User_ID (primary)| Name | Address | Email | Username | Password |

Table 2
| Product_ID (Primary Key) | User_ID (Foreign Key) |

Is this a better way to make table two in which the user ID is not repeated? I have thought about having a separate table in the database for each user, but from all of the other questions I read on StackOverFlow, this is not a good idea.

The constraints I am working with are 1-1000 users and Table Two will have approximately 1-1000 indexes per user. Is there a better way to create this set of tables?

score 0 · Answer 1 · answered Nov 26 '16 at 17:03

I don't see NF2 violated. It states:

a table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table.

quoted from Wikipedia article "Second normal form", 2016-11-26

Table 2 has only one candidate key, the primary key. The primary key consists of only one column. So, there is no proper subset of a candidate key. So, NF2 can't be violated unless NF1 is not fulfilled.

score 0 · Answer 2 · answered Nov 26 '16 at 17:09

0

you says "to make table two in which the user ID is not repeated"

then why you dont do

Table 1

|User_ID (primary)| Name | Address | Email | Username | Password | Product_ID ( Foreign Key nullable)|

Table 2

| Product_ID (Primary Key)|

answered Nov 26 '16 at 17:09

Esperento57

16,521
3
39
45

I would need to query the second table for all product IDs that would "belong" to which ever user ID. Is there a way to do that with this set up that I am not aware of? – VashTheStampede Nov 26 '16 at 18:51
of course you can, with SQL left outer join by example – Esperento57 Nov 26 '16 at 19:22

score 0 · Answer 3 · edited May 23 '17 at 12:08

There's nothing wrong with a value appearing many times. Redundancy arises when two queries that aren't syntactically equivalent always both return the same value. Only uncontrolled redundancy is bad. Normalization controls some redundancy by replacing a table by smaller ones that join to it.

Normalization decomposes a table independently of other tables. (We define the normal form of a database as the lowest normal form that all of its tables are in.) Foreign keys have nothing to do with violating normal forms.

Learn what it means for a table to be in a given normal form. You will need to learn a definition. And the definitions of the terms it uses. And the definitions of the terms they use. Etc. A table is in 2NF when every non-prime column has a functional dependency that is full on every candidate key. Also learn the algorithm for decomposing a table into components that are in a given normal form. Assuming that these tables can hold more than one row, so that {} is not a candidate key, both these tables are in 2NF.

A table in 2NF is also in 1NF. So you don't want "to get it out of the first normal form".

2NF is unimportant. When dealing with functional dependencies, what matters is BCNF, which decomposes as much as possible but requires certain higher-cost contraints, and 3NF, which doesn't decompose as much as possble but requires certain lower-cost constraints.

Normalizing a simple SQL Table

3 Answers3