Right design to avoid duplicate table almost identical (1 Viewer)

[rafa]

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

 

[plog]

Get rid of TbCoinsinSuitcase and make every coin in the database exist in a box--even if it doesn't exist in real box. Put "No Actual Box" in the Namebox field of TbBox for those.

 

[MajP]

In order for us to o properly help you, we need some more detailed information. Please provide the following
Your address
Days you are away from home
Blue print showing rooms and locations of suitcases
Pass code to locks
Location of any security cameras
Name and favorite snack for any dogs
Total value of all coins

However, I agree with Plog. But please provide the info as requested for future assistance.

 

[plog]

Yikes grandpa, get with the times. That's just too much work. All we really need is credit card number, 3 digit code on the back, mother's maiden name and the name of your first pet.

 

[isladogs]

I suggest you scrap the coins in suitcases analogy and provide an outline of the actual project

 

[rafa]

You are very fast answering!! Thanks a lot!

Isladogs, sorry if this not the best way, but let's say that to provide my actual project would be difficult...

Plog, thanks for your answer. You suggest to put all the coins which are not in a box in a fake box? Only in one fake box? But then that fake box must be allocated ONLY in a suitcase... did I get it right?

 

[isladogs]

I give up ...

 

[rafa]

I give up ...

No problem, thank you anyway.

 

[plog]

Only in one fake box?

One fake box per suitcase. That means for every record in Suitcases there should exist a record in Boxes, even if a real box doesn't exist. All records in Coins will belong toa record in Boxes.

 

[Gasman]

Isladogs, sorry if this not the best way, but let's say that to provide my actual project would be difficult...

More than this coins nonsense?

 

[rafa]

One fake box per suitcase. That means for every record in Suitcases there should exist a record in Boxes, even if a real box doesn't exist. All records in Coins will belong toa record in Boxes.

Ok, thanks!
I will do it that way, only needed to add some fields in TbBox and not duplicate a whole table for coins.
Thank you very much again.

 

[gemma-the-husky]

One question first, are coins homogenous, or are they distinct

ie is it enough for you to know you have 10 silver dollars, 3 in one box and 7 in another, or is each silver dollar an identifiable unique coin, so you need to know specifically that silver coins 1,2 and 6 are in box 4, and coins 3,4,5,7,8,9,10 are in box 8. And if you dispose of a coin, you need to identify that coin 5 has cone, rather than just a generic coin.

Having individual coins makes things a bit harder in some ways, probably

In any event, I would consider this from the coin record.
Can a coin be loose, or is it always boxed.

The flow ought to be something like
a coin is in a box, boxes are in cases, cases are in rooms.

that way you can move a case from one room to another, move a box from one case to another, or move a coin from one box to another. The only information you need regarding the location of a coin is the box that it's in. The coin doesn't need to know which room it's in. Only the case needs to know which room it's in. Then you can move a case from room A to room B, and all it's contents which only the case knows about move with it.

Maybe treat unboxed as just another box. That way some coins are in the "loose/no box" box which is in case 4, and you can move (some of) or all the coins from the "loose/no box" in case 4 to the "loose/no box" box in case 6, or to any real box in any case.

It's easier (for the database, and for the designer) to have all the coins in notional boxes, than to have some in a real box, and some just in a real case, just because there is no way of dealing with unboxed coins.

This way you can always count your coins in relation to "boxes" albeit that some of them are in the "loose/no box" boxes, without needing to union the coins in boxes with the loose coins in cases.

(looking back I see this is what plog suggested - the idea of a phantom box)

And in case some coins are loose in a room, stacked on a shelf, (newly arrived coins, say) you probably want a phantom case to hold the phantom box with the unsorted coins!

 

[rafa]

Hi Gemma,

thank you for your remarks:

"One question first, are coins homogenous, or are they distinct"
- All coins are distinct and unique. I need to know specifically where each coin is.
Inside TbCoins there will be fields to make each coin unique.

"Can a coin be loose, or is it always boxed"
- Yes, a coin can be loose. Some of them are just inside a case (a specific case) and some are inside a box inside a case.

"that way you can move a case from one room to another, move a box from one case to another, or move a coin from one box to another"
- Yes, it can be done, but to move boxes or cases it is not needed. We can think that boxes and also cases are too heavy to be moved. Only coins will be moved from box to box, or from box to case and viceversa.

"It's easier (for the database, and for the designer) to have all the coins in notional boxes"
"(looking back I see this is what plog suggested - the idea of a phantom box)"
- Yes, that sounds now perfect for me. As first approach I was thinking to treat coins in a box differently...and I made two tables for two types of coins, which I saw it was not a good way.

Ok, as you and also Plog sugested ("Get rid of TbCoinsinSuitcase and make every coin in the database exist in a box--even if it doesn't exist in real box") now we have not five but four tables: TbRoom, TbCase, TbBox and TbCoin. With this approach (make a phantom box per case) I just will have to think how to show it in forms and reports, to hide in some way that "fake" boxes.

Let me please add another condition: if the number of boxes and case are fixed (I will not add more cases or boxes once I start the database, all will be inside) and as I said I will not move them, and now that we created fake boxes for each coin, in some way we are duplicating some cases (a fake box is unique inside a case). My question is, can we remove the Tbcases? Then, coins are in a box, boxes are in a room, and probably we can group or sort boxes as needed. Or perhaps eliminating this table gives more problems than advantages. Ok, this is maybe another subject, and probably yout answer will be "that dependes on what you need later".

Thanks again!

 

[gemma-the-husky]

You don't need a fake box. Give the boxes names, and call this box "unboxed". It's just a data management device.

It doesn't really matter if cases can or cannot be moved. It's just the concept of a case record. Is it a real thing that contains boxes?
If you only have a single case in each room, (or no real case at all) then you don't really need a case record.
If you can have multiple physical cases in a room, then you do need the case records.

I think a box is really just a location.
I take it your cases have fixed dividers or compartments and you put the coins into the compartments, or maybe into a physical box inside a compartment. If there is a real box, I can't see what stops you moving a physical box of coins from 1 case to another. If there is no physical box, and the case contains compartments, then I can't see how you can have any coins that aren't in a compartment. if you see what I mean.

But you understand the entities you have.
You need to get a 1 to many structure. You can't have many to many and you shouldn't have 1 to 1. If you only have 1 case in each room, this doesn't mean it's not one to many. It means at some point you MAY put more than one case in a room.

room 1 -- many cases
cases 1 -- many boxes/compartments
boxes 1 - many coins

You only want one phantom box per case. A Phantom box really means "coin is loose inside the case", which applies however many coins are loose. So you may have case 6, with boxes 22,23,24,25, where box 22 is the "unboxed" box. So you can put a coin in box 22 (ie lose inside the case). There is only one box 22, and this is known to be in case 6, which is located in room X. You move a coin from box (location) 22 to box (location) 156 (which eg may be the unboxed location for case 77), and everything else takes care of itself.

Now if box (location) 156 is in another room (or case) compared with box (location) 22, you may want to get user confirmation of the action, but that's a different thing. One is the database design, and the other thing is application of rules to manage the data.

Just going back to "every coin is unique". In your model you need to be able to distinguish between "unique" coins. If you have 2 proof 1921 sovereigns, how do you distinguish between the two, unless they are in numbered envelopes for instance? - in which case what you are really storing and managing in your database is the numbered packages, rather than the "unique" coin inside. In any event I would give all your coin records a number (could be autonumber) and make that the PK. Put each coin in a numbered envelope corresponding to that number, or put a numbered label on the coin (without damaging the coin of course). You still need the data fields to record the coin attributes, but you are managing the notional ID numbers you give your coins.

I would think you might use your coin attributes to find the coin(s) you want - eg 1921 proof sovereigns, and then use the data returned to identify the actual storage locations for the candidate coins. So your database would need fields for coin type (soveriegn, krugerrand), condition (proof, fine, very fine, used), year (value) etc. Or you could select a single case (or box within a case), and get a report of all the coins in that location. If you don't have coin ID numbers, I struggle to see how you can reliably uniquely identify a single coin with no possible confusion.

You might be able to find an existing database dealing with things like CD or DVD storage, or wristwatch storage, or maybe philately or cigarette card collecting. The basic entities in those databases are likely to be stored in different containers in a similar way.

 

[JMongi]

This seems to be a case of trying to model the real world (rooms, cases, boxes) instead of modelling the data and using other tools (queries and forms) to model the real world. Using your example - since individual coins are unique and individual coin locations are thus unique a coin can either be in a box or a case. It seems that the following table structure is much simpler.

 

[MajP]

That does not make any sense. A case or a box can have 1 and only 1 coin. And more than 1 box / case can hold the same coin.

 

[JMongi]

LOL, I see it clearly now. I out thought myself (happens too frequently). I was writing this out with a different approach and thought, "Hey wait, this works like this." Clearly not. I see the second point very clearly. Is your first observation a statement about my schema? Why can a case or box only have 1 coin?

Edit: Sorry, I don't mean to thread hijack. If you'd like to respond in pm, you can. Otherwise I'll keep working on it on my own.

 

[isladogs]

This whole thread reminds me of Schrodinger's cat. You have to open the box to determine if the cat is dead or alive.
What on earth this thread is really about remains a complete mystery...

 

[JMongi]

Weirdness aside, I'm using it to train myself in db thinking which my post above shows I need a lot of work still!

 

[isladogs]

That's all fine... but you would have had more focused replies, relevant to your needs, if you had provided the details of your actual database rather than your bizarre analogy