MySQL Normalized Database Design

Question

I have the following table structure. The requirements are that it needs to be normalized to 3rd normal.

I am not sure about the subscriberAddress is that needs to be in a new table or it can just go under subscribers? What do you think? Is the rest fine?

users(userName, userFirstName, userLastName, password, image)
userRole(userRole)
subscribers(email, firstName, lastName)
subscriberAddress(geoLocationLatitude, geoLocationLongtitude)
products(productName,productPrice, productQuantity, productImage)

This question is highly depending on what you are doing and there are various arguments for both sides. Try to ask clear and well defined questions. WHAT are you planning? MAYBE we can give you advice then — swe
– swe, Commented Nov 28, 2017 at 7:28
it´s for a webshop, where you can subscribe and subscribers will be displayed on a page with their info + on a map (all on the same page) — codeDragon
– codeDragon, Commented Nov 28, 2017 at 7:35
Hi. Read your textbook. Normalization to 3NF involves FDs (functional dependencies). What are yours? We can only guess, and common sense isn't typically enough because they are application-specific. Anyway you should be showing your attempts--read & act on hits googling 'stackexchange homework' and How to Ask. Constraints between tables are irrelevant. The two answers so far are worthless. — philipxy
– philipxy, Commented Nov 28, 2017 at 12:20

ditch · Accepted Answer · 2017-11-28 07:39:21Z

1

I cannot see any reference between tables. How subscribers and subscriberAddress are related? If a subscriber has only one address, you can put it into subscribers. If a subscriber can present many addresses, put them into another table with a reference to that subscriber .

answered Nov 28, 2017 at 7:39

ditch

929 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codeDragon Over a year ago

they have only one address.

Kieran M · Accepted Answer · 2017-11-28 11:12:17Z

0

Do the tables conform to 1NF and 2NF first of all? They need to before they can be in 3NF.

Can the table structure be amended? If so I would add a unique identifier e.g. subscriberID, this is the primary key for the subscriber table. This ID can be added to the subscriberAddress, acting as a foreign key.

I'd keep the subscriberAddress table separate.

answered Nov 28, 2017 at 11:12

Kieran M

3717 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL Normalized Database Design

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related