Hi,
Before creating a database that I think it will be complicated I have a question about design and normalization. Please let me explain it with a simple example. I hope it is not too naive for you. Here we go:
I keep coins in suitcases inside several rooms. For this distribution the tables are clear. TbRoom (one-to-many) TbSuitase (one-to-many) TbCoin.
But...in some suitcases I keep the coins sorted in boxes inside the suitcase, and sure I need to know which coin is inside which box.
All coins need the same fields: both those that are kept in suitcases and those that are sorted in the boxes inside suitcases.
For this distribution I added two additional tables: one is clearly TbBox, and another goes forTbCoins-in-Suitcase-in-Box, which in practical has the same fields like TbCoins plus the ID foreing key number field for the one-to-many relationship with TbBox (which in turns is one-to-many with TbSuitcase). Please see below the design.
Can you see my concern? I have two tables for coins. I think this will work without major problems but...is this the best design for this situation? Can it be design without repeating the fields for the coins?
Looking at the level of the questions/answers in the forum, I guess this is pretty basic to almost all of you. If my database would be not as complicated as I hope it will be, I would not have asked myself this question and would have gone ahead.
Thank you very much in advance for your advice!
Rafa
Before creating a database that I think it will be complicated I have a question about design and normalization. Please let me explain it with a simple example. I hope it is not too naive for you. Here we go:
I keep coins in suitcases inside several rooms. For this distribution the tables are clear. TbRoom (one-to-many) TbSuitase (one-to-many) TbCoin.
But...in some suitcases I keep the coins sorted in boxes inside the suitcase, and sure I need to know which coin is inside which box.
All coins need the same fields: both those that are kept in suitcases and those that are sorted in the boxes inside suitcases.
For this distribution I added two additional tables: one is clearly TbBox, and another goes forTbCoins-in-Suitcase-in-Box, which in practical has the same fields like TbCoins plus the ID foreing key number field for the one-to-many relationship with TbBox (which in turns is one-to-many with TbSuitcase). Please see below the design.
Can you see my concern? I have two tables for coins. I think this will work without major problems but...is this the best design for this situation? Can it be design without repeating the fields for the coins?
Looking at the level of the questions/answers in the forum, I guess this is pretty basic to almost all of you. If my database would be not as complicated as I hope it will be, I would not have asked myself this question and would have gone ahead.
Thank you very much in advance for your advice!
Rafa