not sure how to normalize my db

[raistlin]

The db I'm working on currently has 6 tables; tblAssets, tblManufacturers, tblModels, tblSites, tblStaff, tblTypes. This is my first db and I'm slowly learning how to properly use Access.

Right now I have all my information in tblAssets and only 1 column in each of my other tables except tblStaff. So for example, tblSites just has a list of sites like Site1, Site2, Site3 with no additional information in the table. I originally did this because I thought I was "normalizing" my db by having additional tables like that, but the more I read tutorials and other tips about normalization the more I think my db is way outa wack.

To me it seems pointless for me to have the tables tblManufacturers, tblModels, tblSites, and tblTypes now, but at the same time it doesn't seem right that I have so much information in just one table, tblAssets. The information ranges from computers (manufacturer, model,..., CPU, RAM,...), Printers (manufacturer, model,...), projectors, routers/switches, etc.. So I'm trying to figure out if it's best just to leave all the information in one table, or to separate it and make a table for each type of product, or separate it some other way (not sure how else to do it).

If someone could point me in the right direction with this I'd greatly appriciate it. I have read many posts and tutorials on these forums about normalization, but I'm still not quite sure the best way to go about it with this db. The microsoft "assets" database template only has 1 table for all of it's assets, but it just seems like I have too much information for each product to make that a good solution.

thanks for your time.

 

[georgedwilkinson]

Can you post your DB?

How To Upload A Database To The Forum

 

[raistlin]

Ok, here's the tables I currently have.

 

[The_Doc_Man]

In general, what you described - multiple tables with data segregated by entity type - is quite reasonable as an approach.

Sometimes a single-field table is still proper when used as a lookup or validation table, because that makes it easier to maintain the list of valid choices.

When you normalize, here's what you are REALLY trying to avoid...

Let's say you have assets at store #1 and other assets at store #2. If you didn't have a site table for store #1 data and store #2 data, you might have to include the site name, site address, etc. in the main table. This way, you only have the site number and can look up the rest of the data from that site number - using a JOIN query, for example.

So whenever you want to prevent repetitive data entry of things that will be common to lots of records, that justifies splitting tables and forming a relationship.

In your Assets table, I could see relationships of various fields, since an asset surely has a site, a type, a manufacturer, probably has a model designator.

In your Staff table, I can see a relationship to site since you need to know where the staff members work. If an asset is signed out to a staff member, I can see a junction table recording the assignment. So there's not a thing wrong with where you are going conceptually.

Therefore, if you have qualms, it must be about implementation details that you have not revealed to us, or perhaps just a lack of self-confidence. (Been there, done that, bought that T-shirt....) Let me reassure you that no alarm bells went off in my head at reading your original question. It sounds to me as though perhaps you are just a bit unsure. Now, at some point those other tables would probably be filled in with stuff besides the key columns, and I would fully expect that you will develop those tables as you go. I'll avoid getting too long-winded about it until you have more specific questions. I would say you are not going too far astray based on what you told us.

NOTE: I cannot download databases to this site (my day job) so cannot review the zipped file to take a closer look. Therefore, I base this response on what you said.

 

[georgedwilkinson]

OK, the basic structure looks good, but you need to normalize further and add some "beef" to some of the tables. I'll give you some general stuff and then dig down into your specific design. Just because I put something in the general section doesn't mean I think you've done that wrong...just putting out the info.

A general rule of thumb for putting fields into a table: if it is born with it, or will die with it, or both, it belongs in the table. If it does neither, but it still related in some way, it goes in a separate, related table. If it is "born" with something but there is a likelihood that it will die with a different something, it goes in a seperate related table. If it uniquely defines one of the entities in the table, it may belong in that table, as long as it doesn't severely break one of the other rules. For instance, there are a bazillion people named John Smith. What uniquely identifies each of them (in most systems it could be a SSN or potentially DOB/DOD, but it is NEVER address related from a system perspective).

I'd also like to talk about naming tables and objects. Name tables with names that are incredibly indicative of what data is contained in them. For instance, a table named "Types" doesn't say what's in it. In your case, naming it "AssetTypes" is a better name, because it tells you what is in the table. Always use plural names for tables and singular names for column/field names in tables. Never use special characters or spaces in your table/field names (with a possible exception of underscore-"_"). And the prefix "tbl", at the cost of potentially being flamed, I don't use it because it makes talking/thinking about the design more difficult and in a RDBMS centric system the table design is the most important part (I do use a prefix religiously for every other object in my systems, however). Do whatever you want on that.

On to specifics:

Go down the list of fields in your Assets table and ask if each field breaks the rules above.

NetworkName, MAC(1&2) (breaks special character rule and normalization rules), NIC(1&2), Location/Site (this thing moves around but this could be an instance where breaking the rules is acceptable), IP (could be multiple IPs for each MAC, right?), and all the stuff about HD/RAM/Monitor/speaker/etc. are not part of the asset...in fact, aren't some of them potentially assets?

So, where do you put all this stuff if not in the Assets table? I recently did a drive-by on a thread on this forum that discussed MAC addresses and IP addresses. There was more said there about that topic than I have time to type but that is the general idea of how to deal with this stuff. I believe I suggested a table named something like ComputerAddresses or something like that. Potentially with a ComputerAddressType field constrained by a look up table (ComputerAddressTypes).

You'll need to do something like that with all your fields that don't "belong" in the Assets table.

A hint for the other fields that are "part" of the asset is to have an AssetComponents table with a FK relationship from the Assets table.

So what about other assets you have listed as fields, aren't they assets? But if you have them in a separate row of the same table, how do you know that they are currently "attached" to this particular asset?

There are 2 ways to attack this...one is to add a field to the Assets table with a self-join...call it something like "ParentAsset". The other is to create an associative/junction table with 2 relationships to the Assets table and a "type" field (or two) indicating what the relationship is, potentially with dates and notes. It's really up to you, but I prefer the second option.

My wife is making me go eat. This should get you moving. Yell if you need help.

 

[Mike375]

The classic example used for Access is Customers and Orders.

In a nutshell one entity can have many "different descritipions" and what you avoid is that situation in one table.

For example, if you were making a personal contact data base you might inlude details on the persons children, details on meetings or appointments and so on. If all this was on one table then you run into the problem that you would need fields like:

ChildName1, ChildGender1, School1, DateofBirth1 and then ChildName2, ChildGender2, School2, DateofBirth2 and how far would you go. What if somone had 7 kids. How many fields do you allow for. Then there are difficulties with getting backing information. Say you wanted the average age of all the children?

Thus we would have a table with children details. We would have a table for appointments and meeting details.

Another way to think of it is a doctor and his patients. If he did it on Access then "one box of cards" would have a card for each patient and their basic details such as gender, date of birth etc. A second "box of cards" would have one card for each visit the patient made to the doctor. Thus as his practice got larger he would need to get bigger cards and wider boxes......he would get more cards and longer boxes.

The nature of your business obviously comes into play. For example, I ma in the insurance business and 99.9% of my dealings are with individuals, that is, i don't have corporate type customers. So I have a field for business ph, one for home ph and one for mobile phone and one for fax. But of my customer or client base was corporate then I would have another table that was for phones and faxes.

In short, if a table has fields for data of which there can be multiple instances of the same type of data and especially if the number of instances is an unknown, then that goes in another table.

 

[raistlin]

Thank you all very much for the advice. The descriptions and scenarios that you guys have used made it much easier for me to understand how to separate everything into different tables. I'm going to play around with it a bit and see if I can come up with a better structure for my tables, and then I'll post it again when I'm happier with the results. Cheers!