1

I'm new in database normalization and I want to know if I'm doing the right thing about the structure of the table that I created. I have a table called "RoomRates" with next columns: 

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

As far as I know 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 breaking First Normal Form ?

Nabucodonosor10
  • 13
  • 3
  • possible duplicate of [A beginner's guide to SQL database design](http://stackoverflow.com/questions/377375/a-beginners-guide-to-sql-database-design) – Walter Mitty Jul 10 '15 at 11:57

2 Answers2

1

Fastly, I will provide you some tips. I recommend do not walk step by step throught normal forms, because you will do re-make your previous work (and your team's work), unless you are practicing.

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

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

I recommend to you take a look to this link: A beginner's guide to SQL database design

Hope this helps.

Community
  • 1
  • 1
Fer R
  • 141
  • 2
  • 9
1

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.

BND
  • 612
  • 1
  • 13
  • 23