Are my database tables normalized?? 2

[remsaw]

Hello -

This is a lengthy post, and I appologize beforehand. I have looked at many posts on this web site, and went to

http://r937.com/relational.html

and read/re-read that quite a bit. I have asked all the users of my database some questions regarding what information they want to obtain from a large pile of data currently somewhat maintained in a variety of flat file spread sheets.

I track a large number of assets which consist of a particular government system and the various equipment to test/repair/and develop improvements. The assets range in variety of actual test equipment such as multi-meters, tools, hard disks, software, servers, processors and specialized stuff.

The "mission statement" of the database is as follows: "This database will track assets and give real time status regarding location, security classification, operational status and revision level.

So far my tables are broken down as follows, and I think I am in 3NF:

tblEquipment:
pKPartNumber
pKSerial Number
Revision
Barcode
fkSystemID
fkLocationID
Classified (Y/N)
fkStatusID
DateRcvd (Date)
fkSanitizeID
Comments

tblLocation:
pKLocationID (Autonumber)
Location
fkCustodyID

tblCustodian:
pKCustodianID (Autonumber)
CustodianFName
CustodianLName

tblSanitize:
pKSanitizeID (Autonumber)
SanitizeProcedure

tblNomenclature:
pKNomenclature
pKPartNumber
Hardware (Y/N)

tblStatus:
pKStatusID (Autonumber)
Status

tblSystem:
pKSystemID (Autonumber)
System


The following are specific questions I hope to be able to answer from this mountain of data:

Where is the item? Is it sub-custodied to a person? If so, to whom?

What system is it part of? (On any particular day a particular item might be implemented in various systems, however, a particular item can only be implemented in one system at a time.)

Does it contain government classified information at this time? If so, what are the santiziation procedures?

Is it software or hardware? What is the revision level? (Revision level is more important for software, but could also be a hardware question.)

What is the operational status? It could be fully operational, partially operational, or completly inoperatonal. I envsion the comments field in tblEquipment to detail this specifically.

Thank you in advance for your time in looking at my fumbling around. I am a pretty new database designer, have never tackeled a project this large before. I have however worked with poorly designed database solutions before, and do not wish to do that again.

Scott

 

[Remou]

A few thoughts, on a first glance.

You can only have one primary key per table.

I think you might be better having a tracking table:

tblEquipment:
pKPartNumber
fkLocationID
...

tblLocation:
pKLocationID
Location
fkCustodyID

tblCustodian:
pKCustodianID
...

tblLocationCustodyHistory
DateField ) I think this could be the primary key
PartNumber )
CustodianID
LocationID
Comments

You may wish to include updated dates and updated by fields.

I suspect Classified (Y/N) is insufficient. You may need a separate table with some details, such as date declassified.

I cannot see an operating system field or a product key for software, nor are there tables for suppliers and such like, and wonder if you should make provision for the database to develop in complexity.

 

[Golom]

Remou

Just a minor point of disagreement. I think remsaw has a multi-field primary key in tblEquipment and tblNomenclature so it's not really more than one primary key ... just a PK composed of multiple fields.

remsaw
As a first attempt ... you've done a great job. I have seen designs by "qualified" database designers that were not this well thought out.

The inclusion of
[tt]
pKPartNumber
pKSerial Number
[/tt]
in tblEquipment does imply that pKPartNumber is not unique. Is that really the case? If it is then tblNomenclature is going to have problems. It will be uncertain as to which record in tblEquipment is being referenced. If pKPartNumber IS unique then it should be the only field in the primary key. If you review the document you will notice that foreign keys need to reference the whole primary key (or at least a candidate key) to uniquely identify a record.

I assume that fkCustodyID in tblLocation is a foreign key reference to pKCustodianID in tblCustodian. Is that correct? If so, it would appear that it is impossible for an item to be in someone's custody but it's current Location is unknown. Is that the case?

I do agree that the structure as it now exists doesn't allow historical reporting in the sense that there are no time stamps that indicate when a particular property of the part number changed. That may not be of interest but you might want to review that requirement with the users.

 

[remsaw]

Thank you both, Remou and Golom for your great inputs. I have made some changes regarding history.

Remou - I had initially thought perhaps I could do something like run reports and use that date/time stamp to track my history. That of course would lead to having to save a bunch of reports, probably not the best idea. I also added a table regarding date classified, source, etc. Regarding the history, I have some customers who are suppliers also, so I have included tables for that, too.

Golom - You were right, tblEquipment has a multi field pK. The question of the part number not being unique, they are not always unique. I have more than one item of some of the parts. For example, I have five multimeters of the same partnumber, but different serial numbers.

Regarding the problem with tblNomenclature and tbleEquipment, namely the fact that tblNomenclature will not know exactly which item in tblEquipment is being referenced, I do not see that as a problem. To give an example, we have several types of multi-meters. They have several part numbers, F-87, F88, etc. I only envision tblEquipment needs to know the nomenclature of a particlar part given a part number.

I had thought of simply using Barcode as the pK. However, not everything has a Barcode - probably about 90% of the items do. But perhaps I could make a "Barcode" for the items which do not have one. I thought perhaps a simple Code routine which would generate a seriese consecutive numbers and then append that number to a string such as "zzz" - this would make a unique barcode, and also let my quickly select in or out the items with "real" barcodes.

This is a real lengthy post I realize, but here is my modified table structure:

tblEquipment
pKPartNumber pKSerialNumber Barcode Revision fkStatusID fkClassID fkSanitizeID DateRcvd Comments

tblLocation
pKLocationID Location

tblCustodian
pKCustodianID CustodianFName CustodianLName

tblLocationCustodyHistory
pKTransactionID fKPartNumber fKSerialNumber Ship/Rcv (Y/N) fKCustomerSupplierID fKLocationID Comments

tblSanitize
pKSanitizeID SanitizeProcedure

tblNomenclature
pKName pKPartNumber Hardware (Y/N)

tblStatus
pKStatusID Status

tblSystem
pKSystemID System

tblClasssified
pKClassID ClassLevel ClassSource ClassDate DeclassDate

tblCustomerSupplier
pKCustomerSupplierID fkContactID Organization ShipAddressStreet1 ShipAddressStreet2 ShipCity ShipState ShipZip

tblContacts
pKContactID ContactFName ContactLName ContactAddresStreet1 ContactAddressStreet2 ContactCity ContactState ContactZip ContactEmail ContactTel

 

[Remou]

A quick thought. Why not a 'persons' table? Details for suppliers / customers / contacts are the same for the most part, that is, name, address, tel etc. A 'person type' field will allow you to distinguish between contact types, bearing in mind that in some industries a person can be both a supplier and a customer. You may also wish to include a 'person status' field to indicate that a person is deleted or current, because you will need to keep the details for historical reasons.