Database design question

[elizabeth]

I've created a database for tracking network gear inventory. I'm not too familiar with network gear, but my subject matter expert tells me that a Chassis has Interfaces. Problem is, a Chassis may also have Cards, and Cards have Interfaces too. Does this situation call for anything different from multiple 1:M relationships?<br>
<br>
ex:<br>
Chassis = 1: Interface = M<br>
Chassis = 1: Card = M<br>
Card = 1: Interface = M<br>
<br>
Wish I had a way to draw or attach a drawing here!<br>
<br>
I want to make certain that if an interface is on a card, it cannot be on the wrong chassis. I don't know if I should just use a form with a combobox for[ tblInterface]![Card] that allows only the cards that are on that chassis, or if there is some other standard way of handling this.

 

[bitbrain]

You are on the right track – that is – defining your entities and the relationships between them.<br>
<br>
You need to determine if a given entity is autonomous (can exist by itself) or if it is dependent on another entity. For example, is Card a further definition of Chassis? If so, then the primary key for Card would be a composite (compound primary key) of the Chassis primary key and a Card id. Since the Card primary key (which is compound) will be included as a foreign key on Interface, the Chassis Id on Interface will have the dual purpose of pointing to Chassis and Cards - thereby insuring that Interface is associated with the correct Chassis.<br>
<br>
Or something like that…<br>
<br>
<br>

 

[elizabeth]

Thank you. The database contains installed and uninstalled inventory, so a card is autonomous. A chassis may (or may not) contain one to many cards. A card may be switched from one chassis to another. I'm thinking of the chassis being the current location of a card, and thought I'd set up a dummy chassis to use as the location of all uninstalled cards. So if an interface is on a card, great; as you've said I've got the chassis too. But the interface may be directly on the chassis. Only certain types of chassis accept cards.<br>
<br>
And speaking of compound keys, I've gotton into the habit lately of making every key an autonumber. This is because I have loads of lookup tables and seems the users always want to change those values. I use combo boxes in the table design to display the lookup values instead of the IDs, to keep it pretty, but I wonder if I'm sacrificing performance here.

 

[bitbrain]

Keep working on it. You should not have to set-up "dummy" anything - your design should be able to handle all situations (within scope). Sounds like you are close, don't give into the dummies!<br>
<br>
Some of your rules are complex and may need to be enforced through code. I don't think the db design will cover all the rules.<br>
<br>
As far as keys go... From a modeling perspective, primary keys should be values that are unique and NEVER change. Access makes it easy to have changing primary keys but try and stick to the modeling rules. Using AutoNumber (long integer) keys is actually better from a performance perspective (joins are more effcient). I think your approach is right on.<br>
<br>
<br>

 

[elizabeth]

Thank you again. It's really helpful to hear that I may have to use code to enforce rules. I wasn't even aware of the implicit question I was asking "should I be able to implement all rules through the design alone", which is what I've been trying to do. I think I'm all set on that now.<br>
<br>
But I'm confused by your statement to avoid dummies. I didn't realize that was a bad thing. A card can exist in 3 locations, according to my business rules: <br>
(1) at a manufacturer (a return or trade-in; we can't eliminate it from the database because we have to track replacements, service contracts etc.)<br>
(2) in a chassis<br>
(3) in a storage location<br>
<br>
I have a similar situation with chassis which can be located at the manufacturer, in a wiring closet, or in a storage location. The Location table is set up as Bldg/Floor/RoomName, and I use a fake Location called Returned/to/Factory, so that I can make Location a required field and enforce referential integrity between Location and Chassis. Do you mean this should be handled with code instead? <br>
<br>
Hmmmm.... I guess I need to scout around for a good design book. My designs are getting a little too complex for me (this one has a dozen data tables plus about 20 lookup tables). Know any good books you'd recommend? I've got several but they are pretty elementary about the architecture, just reviewing normalizing.

 

[bitbrain]

I may have over reacted to the term “dummy.” The scenario you described sounds like a ligit business rule. Sometimes people use dummy data to get around design limitations. For example a database may be designed to only allow one phone number per person when in fact people can have multiple numbers. A user may add a “dummy” person as a way of storing more than one number…<br>
<br>
Try to find a book on data modeling - a.k.a. Entity Relationship Analysis (ERA). Understanding the rules of normalization is important. Keep in mind that the old (in my experience) way of normalizing a database was to start with the data items (fields, attributes). Then organize them into groups (tables, records, entities) and then analyze relationships between the groups. This can be a difficult process.<br>
<br>
Newer data modeling techniques turn the process upside down. Start with the entities, analyze their relations to each other, and lastly fill them in with attributes. Normalization is a by-product of this process – the end result is the same but the method is easier.<br>

 

[elizabeth]

thanks for the examples. i am also scanning (not-to-be-mentioned on-line-bookseller) for data modelling books but so far haven't found anything that mentions recursion, which i also need to understand.