Normalization

[scouser]

I have decided to normalize further and now have brain freeze. I have a table 'tblComputers'. I have removed Computer Make + Computer Model to own tables and linked via ComputerMakeID & ComputerModelID, this is working OK.

I then decided to remove RAM to its own table. I created tblRAM. I added additional rows in tblComputers 'Memory Slot 1' Memory Slot 2' etc. tblRAM has 2 columns RamID & RAM so....RAMID 0 = 0 RAMID 1=128 RAMID2=256 etc..

My issues? What to link RAM to relationship wise then allow users to select multiple instances for different memory configurations to save values back to 'Memory Slot 1' 'Memory Slot 2' in tblComputers?

Hope that makes sense?
Regards,
Phil.

 

[The_Doc_Man]

There is such a thing as overdoing a table split for normalization. You need to understand the "dependency" rule that is part of the normalization process.

You've got a table. It has data in it. If there exists a field among the many of that table, and that field provides a unique identifier for all the data such that every data element in that record is dependent on the field and nothing else, that field is a candidate key. Once you have a candidate key, EVERYTHING that depends exactly and only on that key belong in the same table. Splitting beyond that point is contraproductive.

If you are talking about multiple configurations being manufactured and sold, you would make a table that lists some kind of configuration ID number and then goes from there as the basis. If, on the other hand, you are describing as-built computers at your site, you probably would base everything off the serial number of each computer.

For instance, my system was an HP Pavilion, but that doesn't help because that is a big, wide-open product line. BUT once you add that I started with an A265C, you are getting much closer to specifics. (I've tweaked it with a memory and disk upgrade, so that number is no longer fully valid, but it is close.) If my wife's machine had been the same as mine originally, the only candidate key would have been the S/N.

Now... as to your RAM SLOT 1, RAM SLOT 2, etc.... that has to be a different table altogether. You need a config table. As you described it, RAM SLOT 1..RAM SLOT n represents a repeating group, which is a normalization no-no. Violates 1NF instantly.

I don't know a good answer for your design since you are dealing with disparate part types. For instance, it makes sense to describe amount of memory in a memory card, but if you are dealing with a network interface card, the amount of memory (perhaps in an on-board cache) is misleading because it isn't a general system resource. Ditto video cards, disk controllers, printer controllers, serial line controllers, etc. You have to decide what you want to store and how you want to handle things when you store them.

This is where stepping back and designing your goals BEFORE you design your application will be of vastly great benefit. (Under the "pay me now or pay me more later" theory of project implementation.)

 

[scouser]

Thanks for the reply doc. I have opted to have a table tblRAM which lists various configurations i.e. 0 / 64 / 128 / 192 /256 etc...............
I then allocate total ram installed rather than concern myself with individual memory slots.
Thanks,
Phil.

 

[Moniker]

An easy design suggestion for this is to use the computer S/N or other very descriptive piece of information (as suggested by Doc) as the base of your structure. It's the primary key, the driver.

From there, each major component becomes a table with possible lookups. For example, you would have a videocard table that might look like this:

t_VideoCards

VCardID   VCardBrand   VCardModel   VCardInterface   VCardRAM
1         ATI          2900XT       PCI Express      512
2         NVIDIA       6200GS       AGP              128
.         .            .            .                .

Do this for each major component (Motherboard, Network Card, Sound Card, Disk Drives, Monitor, etc.). Obviously, each component is going to have a slightly different list of details. A monitor, for example, won't have a RAM count or interface type, but it will have Max. Resolution, Refresh Rate, Type (CRT vs. LCD), etc. The Network Card will probably have Type (wired or wireless), speed, etc. You'll have to determine what you want to track for each component.

"Brand" and "Model" will be common between each component -- they are all some brand and have some model identifying information. Therefore, you may what to make a table just for that:

t_BrandNames

BrandID   BrandName   BrandModel
1         ATI         X800XT
2         ATI         X1300
3         ATI         X1800
.         .           .

That table may get large, but it follows that rule of design that states tall and narrow (few fields, lots of records) is better than fat and short (lots of fields, fewer records).

Finally, what you're storing about each machine becomes very normalized, and would look something like this:

t_Machines

MachID   MachineName   VideoCard   NetworkCard   Monitor   (etc.)
1        WorkMachine   27          12            41
2        HomeMachine   4           16            26
.        .             .           .             .

The "MachineName" field is something you make up as a descriptor to uniquely ID a machine in English so that you aren't stuck with a bunch of numbers. From there, you use each component's table to get the details about them. For example, on WorkMachine, you look up VCardID #27 to get its details, NetworkCardID #12 to get those details, etc. You can get as detailed as you want, and it's stored minimally and normalized.

 

[scouser]

Many thanks for the the detailed response, your time is very much appreciated. I will give a great deal of thought to the current table design.

The database in question is a labour of love. I will post the revised version as a sample DB when I finally decide I can let go!!
Many Thanks,
Phil.