Help with Normalization 1

[gmagerr]

Hi,
I'm struggling with normalizing a database I've created. I basically attempting to create an asset management database for my computer center. I've attached a screenshot of what I have so far. I'm trying to depict several things.

Category - Server, Network, Storage SAN, Storage Other, Misc
Suppliers - HP, Dell, Cisco, Etc.

Assets - These are the actual assets, and here's where I'm having trouble. I need to add specifications to the assets like, is it physical or virtual, what row and rack is it in, what is the buisness impact, you can see all of this in the screenshot.

Anyway, if someone would be so kind as to assist, maybe point me in the direction of some good resources, make some suggestions. I've been to

http://www.databaseanswers.com

and look at all of the data models, it's a great site.

Thanks

 

[Thargy]

righto,

I'll dive in then - nice to see someone thinking about normalisation by the way.

So, do staff really work in more than one department? If they don't then the staff department table is redundant. Since you have a deprtmentid in the staff table anyway, what's the point?

The reportsto field in the staff table should be a reference to a staff id in the same table. After all, everybody has to report to somebody. If the CEO is in the staff table, it's acceptable for that record to have a null in the reportsto field, otherwise it's mandatory (i.e. it needs a not null constraint).

The business impact table appears to be a waste of time. What is its purpose? If it's to assess the business impact of the loss of a piece of equipment, then it's completely pointless in your schema - that's a design problem for your senior technical people - not a database administrator.

there is duplication between locations and suppliers tables, since they both store the same sort of info. Lose all the address and postal info from the suppliers table, and just give each supplier a locationid.

The table physical/virtual is superfluous. You just need a boolean field in the asset table called IS_PHYSICAL. If this is set to true, it's real, if false it's virtual. Lose the redundant table.

The assetstaff table seems a bit of a waste of time. What's its purpose? Does it indicate which staff member is responsible for which hardware? If it does, then this may belong in the database, but seems more to belong in a skills matrix for your I.T. manager than in a database.

Let me know what you make of the above, and please provide any clarification that you can.

Regards

T

 

[gmagerr]

Thargy,

Fantastic, thanks for all the useful input. The database started out relatively simple, as I read more about normalization, the more I tried to seperate everything. I'm going to re read your suggestions and apply them to the DB. i'll post the new model.

thanks again

 

[gmagerr]

Thargy,

OK, I've redesigned, and this is what I have now. I renamed the locations table to addresses. I'm a little unsure of that as we have three major locations, would the way i currently have the design satisfy that? in other words is it ok to use the addresses table for the suppliers addresses, and the addressses of where the assets are, or should I create a seperate DataCenter table? The AssetStaff table is there because I need to know who the contact for an asset. this is useful when an asset needs to be replaced or repaired, I need to know who to contact. The design is much simpler now, so how is it looking? Should i break out some items from the assets table? or does it look ok?

Thanks again

 

[Thargy]

gmagerr,

that looks much better. Note that the self-referencing staff table now has what is colloquially referred to as "a pig's ear" in your diagram. If you hear this term bandied about, you will now know what it means.

The whole thing is indeed much simpler. You are correct to just have an address table for all addresses, regardless of who uses that address, don't have a different table for it.

You can delete the address field from the assets table, as the suppliers address can be obtained from the supplier table, which references the address table. Unless there's a separate business requirement to store this info, it's redundant in the asset table.

Drop the 'otherdetails' field from staff. Such fields inevitably become used as a general dumping ground for stuff that nobody could be bothered to identify properly. Sooner or later this carbuncle will be used to store vital business information, and become a gaping sore in your schema design.

W.R.T. the staff asset table, I don't question the design, I question the business requirement for its existence. Knowing who to call is all very well, but support is normally governed by a rota, with the on-call person having a mobile and laptop with remote access. Can you guarantee that the same person will always be responsible for support, including out of hours? If not, then drop the table, and in the asset table, have a filed which stores the emergency callout number, and leave it at that.

Once these changes are made, you should proceed to ensure that you are using correct data types and that constraints are applied to the tables and their fields, to ensure data integrity.

Regards

T

 

[gmagerr]

Thargy,

Thanks again for the input. I will make the changes you recommended and check the data types.