CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-normalizationfirst-normal-form
Nabucodonosor10
Nabucodonosor10

Reputation: 21

First Normal Form Database Normalization

I have a table RoomRates with columns

RoomRateId (int), RoomType (int), Season(int),
Monday (decimal), Tuesday (decimal),
Wednesday (decimal), Thursday (decimal),
Friday (decimal),
Saturday (decimal), Sunday (decimal)

I reason that this structure of the table is breaking First Normal Form.

I have a foreign key on RoomType and Season.

Should I turn table's structure into:

RoomRateId(int), RoomType(int), Season (int),
DayOfTheWeek (int), Rate(decimal)

in order to not break First Normal Form?

Upvotes: 1

Views: 640

Answers (2)

Fer R
Fer R

Reputation: 149

I recommend you do not walk step by step through normal forms, because you will redo your previous work (and your team's work), unless you are practicing.

As a general rule designing databases, each field must exist once in the database. You also must group semantic data into tables, and link them with a foreign key.

I could do a full remake of your question's tables. You probably don't need a column for each day of the week. Did you think about whether instead of several columns you can use only one? The foreign key would be as light as possible (one column recommended, unless there does not exist any other way). Always think about whether your design will be useful for a huge amount of data, in terms of querying and storing.

Look at A beginner's guide to SQL database design.

Upvotes: 0

BND
BND

Reputation: 678

To me your table is not breaking the 1 Normalized Form. The first form 1NF has the following characteristics: no ordering, neither left to right for columns not top-to-bottom for the rows. Every intersection has only one value of the specific domain and nothing else. But the most important characteristic, I think of a relation in the first form is simply that all attributes in that relation should only have atomic values. Attributes with non atomic values should be decomposed to reach the 1NF.

I think you table columns will have atomic values only.

Also, the idea of a column for the day of the week is better than several columns, leading to much storage space and more processing time.

Upvotes: 0

Related Questions