Comments on DB Redesign?

[rokuk]

I'm trying to re-design a database I "inherited" (gasp-shock-surprise, eh? ).

The current design has just one table with about 20 fields in it... I'm no database-design-fu black belt, but that sounds all kinds of wrong. I'm hoping to get some comments on my attempt at re-design.

This database is for tracking inventory (mostly computer servers). Each asset in inventory has it's own row, and many attributes covering it.

Here's an example of the current design:

--------------------
CURRENT DB

- (autoNumber) (the PK)
- department#
- ID# (a business-related value, not the PK)
- brand
- model
- state
- city
- address
- softwareName
- softwareVersion
- owner
- administrator
- hostname
- IP address1
- IP address2

---------------------


That is a good smattering of the challenges I have going for me for examples of fields. I have these main constraints:

- each asset can have only one of the following:
> ID#
> brand
> model
> physical location (state/city/address fields)
> softwareName
> softwareVersion
> owner
> administrator

- each asset can (theoretically) have infinite IP addresses and/or host names

- each asset can (theoretically) have more than one department# (in practice, this will range between one as the vast majority but may range up to about 10)



The following are supposed to be enforced as "unique" fields... but there is nothing in place to enforce this and in practice they are not always unique:

- IP Address1 and IP Address2 fields
- hostname field
- ID# field

There are no other constraints I am aware of.


-----------------------

My issue was this: almost everything I look at related to this kind of design has multiple entity / relationship sets. Those seem to make sense. However, in this case if I think of each asset as an "entity" the only relationship I can really see is a "HAS" relationship to all these fields, which is what they currently all are - attributes in one giant table. This doesn't seem quite right though.

In doing my reading I've come across the following ideas:

> break out IP Address fields and hostname fields out to different tables (since an asset can theoretically be assigned an infinite number of each).

> possibly create a location table (to store the state/city/address fields)

> possibly create a software table (to store the softwareName and softwareVersion fields)

> possibly create a department# table (to store the department# field)


===========================


So after my reading, I've come to some interesting conclusions. This is my theoretical re-design, please tell me what you think ; ) Lord knows I can only improve this thing...


tblDepartments
- departmentID (PK, autonumber)
- department#
- assetID (FK)


tblLocation
- locationID (PK, autonumber)
- state
- city
- address


tblSoftware
- softwareID (PK, autonumber)
- softwareName
- softwareVersion


tblHostNames
- hostNameID (PK, autonumber)
- hostName
- assetID (FK)


tblIPAddresses
- IPAddressID (PK, autonumber)
- IPAddress
- assetID (FK)


tblAssets
- assetID (PK) > the current "(autoNumber) (PK)"
- ID#
- brand
- model
- locationID (FK)
- softwareID (FK)



That's it. Again, I was hoping to get some feedback to help improve this thing. It makes a decent amount of sense but I'm sure there is a lot of improvement I could think about? I've read these forums for the past few hours as well as paging through an old design book and random websites, but that doesn't leave me feeling as confident as I'd hope ; )

Thanks again!!

 

[gemma-the-husky]

you're getting the hang of it i think - however a couple of thoughrs

tblDepartments
- departmentID (PK, autonumber)
- department#
- assetID (FK)

theis should be the other way round - the department ID should be in the asset table (I asume) - doing it this way means one asset can have several departments, but a department can only have one asset - ie it looks like its the wrong side of a 1 to many relationship

secondly the ip addresses - does each item have a unique IP address (why two incidentally) if so this data could stay in the asset table. (1 to 1 relationships generally dont need separate tables)

finally, you could make the same argument for having the brand and model in lookup tables - indeed the brand may be related to the model

so you might have

asset(recordid, modelref (FK))

model (modelref (PK), brandID(FK))

brand (brandID(FK), manf name)


now you might need an assettype also, to distinguish between types of servers, and other equipment - now you could put this as a direct lookup in the asset table, or instead have another lookup in the model table - possibly the latter is more precise.


hope this helps

 

[rokuk]

.

Thank you kindly for your analysis!

Let me ask some questions... maybe this will help.

---------------------

On your first point (regarding departmentID)... this is what I was thinking. Consider the case if I add the following two assets:
> an asset having multiple departments (lets just say two departments: "deptA" and "deptB")
> then an asset that has just one department ("deptB")


-tbl_Assets will store the new assets (just showing the PK field in the example):
> assetID (PK) = 1 (asset with two departments)
> assetID (PK) = 2 (asset with one department)


- tbl_Departments will have three entries in it:
> departmentID (PK) = 1, department# = deptA, assetID (FK) = 1
> departmentID (PK) = 2, department# = deptB, assetID (FK) = 1
> departmentID (PK) = 3, department# = deptB, assetID (FK) = 2


So far, so good right? This (to me, right now) looks like it means an assetID can have as many departmentID's as needed (one or more), but that a departmentID can only have one assetID at a time (which ...

-OK- let me stop here, I see what you mean. This should actually be a many to many relationship. One asset can have many departments and one department can have many assets? So I'd need a junction table? Let me work on this.

---------------------


For your second point, I probably should not have confused you with that extra information. For my purposes, I can -not- assume that IP Address is a unique field. And the reason it originally had 2 fields is that the DB I inherited had two fields - so technically no reason, really. In reality it should be able to capture anywhere from one IP to many more than one IP if needed. That is why I was trying to set it up in another table - do you think the re-design OK from that standpoint?



And for your final point, you're absolutely right. I thought about the brand/model thing, as it is similar to some of the other relationships. The thing is, that with that it would be fairly more complex to model that (and with data validation people are always adding in new brands and models) so I felt it would just be best to leave it inside "tblAssets." Please let me know if you think this is a mortal sin or if it could just as easily be overlooked.



And finally, I have another question. To properly normalize this (3NF?), should "tblLocation" theoretically be broken down to separate tables for "City" and "Address" if multiple Cities are possible in one State and multiple Addresses are possible in one City? That's more or less a curiosity on my part... but I'd still like to know if that's "academically" the best way to handle that?



THANK YOU so much for the analysis