Help with Tables and NF

[JSimoncelli]

Hello All,
Not sure if this belongs here or in the Theories thread.

Need some help/advice on the Normal Form/Normalization process as it relates to the tables shown in the .pdf below.
I believe I have it down pretty good (I may be wrong); there are two things I need help with.

First in the Master table, the ContractorPuchaseOrder field. This field only comes into play if the contractor purchases an item using contract money. Items can be acquired directly from the customer via signed receipt. This would contain the purchase order (PO) number; there is no other information about the PO needed at this time. So the question, is the location of this field proper?

Second is the Item table. First I am using ItemID as the PK because ItemName consist of things like “Hardware”, “Software”, “Furniture” and the like. The rest are fairly self explanatory. Is this proper? I have my doubts.

Thanks

John

 

[spikepl]

Not possible to tell from the info provided, and not know ing what this is about. Alos, you have not shown any relations, sooo (Edit: almost none of your tables show foreign keys)

I note you have some seemingly redundant data in tblCustomers.. "Location"? Or should it be "LocationID" as in tblLocations? PropertyType is not used anywhare else, neither is Custodian, neither are many of the other tables....

You need to provide more info what all this is about.

 

[Pat Hartman]

Additionally, you have used two names which are likely to cause problems in VBA - Name and Type are both properties and so will cause confusion.

 

[JSimoncelli]

Spike,
Sorry for the lack of information, see the .jpg below. I have changed some of the row information to make it flow better.

The ProtoType table is there to provide a predetermined list of property types. The ItemLocation table is the physical location of the item. The Customer table is a general reference e.g. not a complete address for example “ESR/TRAN BigDog AFB AZ”, a customer can be is more than one location. In the Contract table there is a contract name and a field for both prime contract number and sub contract number. There are time times when there is no sub contract number, for example if I am the prime contractor I will not have a sub contract number.

The Item table is one I have questions about; all of the fields are relate to the itemID. The first field listed “ItemName” refers to the item in a general way, like hardware or Software or furniture. The rest are self-explanatory. It is not necessarily how I would do it but it is a required bit of information.

Let me know if this helps..

 

[Pat Hartman]

Contracts are normally associated with Customers. PropertyType is normally associated with Items. FK names should include some indication that they are FKs. In my databases, the FK name is the same as the PK name that it points to. Only autonumbers are suffixed with ID so any name ending with ID is either the primary key of the table (in which case it would include some part of the table name as its name) or an FK pointing to a PK.

more importantly, I don't understand "Master". Is it supposed to represent an order? Is it supposed to represent Inventory? It seems to have elements of both.

 

[JSimoncelli]

Pat/Spike,
I have made some changes to the structure as you pointed out. I will have to think a bit more about the Contracts and Customer issue.

I agree the name “Master” was misleading, I have changed it to “Item” and I have merged the old items table into the new re-named “Item” table.

This is basically an asset tracking type of database for government furnished property. All of the fields (bits of information as shown) are required. My concern is the Items table passing the 1NF test.

John

 

[spikepl]

Maybe Pat can help you, I cannot, because there is not enough info. You have the entire process and needs in your head, while we only have what you write. We are just guessing/flying blind.

Articulate the process and business objects involved. So far I have no idea what this whole db is supposed to do. Explain like to a stupid child that knows nothing about your work, starting from the beginning. If not able to do that in prose, then give an example. A time line of events, and what happens with which specific data at each step.

 

[JSimoncelli]

Spike,

No problem, sometimes it is difficult to remember not everyone knows what you do on a given subject.

Government Furnish Equipment/Property (GFE/P) is a term used to identify equipment and/or property obtained either directly by signed receipt or indirectly by ordering the item using contract money. The government still owns the GFE/P and the contractor (me) must maintain accountability. As the contractor I may be the “Prime” or a “Sub” contractor to any given contract. Not all contacts have a requirement for GFE/P.

The process flows something like this: when the contractor (me) is awarded a contract, and if the contract has a GFE/P requirement, the details of what GFE/P is needed and how it is to be obtained is worked out between the contractor PI (principal investigator) and the government PM (program manager). And for better or worse I am the GFE/P program manager for our company, and I am creating this database to help manage the program. I must maintain accountability of all GFE; items reside at our office and at other locations. I do not make the decisions on what and how to obtain GFE nor am I involved in the ordering process (creating the Purchase Order (PO)).

As I stated earlier this database is a form of asset tracking. The ItemTag is used to identify each piece of GFE, be it a computer, desk, server ect. All of the fields are supporting bits of information, and are required on a couple of different reports.

The Custodian table lists company employees that are responsible for that given piece of GFE. The Customer table is a list of organizations (not individuals); these organizations or customers are the entities that the GFE accountability process flows through/too. If we are the prime contractor then the customer would be the government organization that issued the contract, in the case we are the sub-contractor the customer would be the prime contractor company. The Contract table is a listing of contract names and both prime and sub contract numbers. It is possible if we are the prime to not have a sub contract number. The ItemLocation table is a listing of locations where the item (piece of GFE) physically resides; it is a more detailed address type table. And the PropertyType table is simply list of predetermined property types. All of the “sub” tables are intended to be editable on the fly by the user (me).

Well I hope this helps and I must apologize in advance if this is TMI. But you did ask …

John

 

[spikepl]

Your narrative still contains blanks for me.

Where do "items" come from ? Since your record has fields with quantities in/out etc? Is one item comprised of more than one unit?

SerialNumber or Serial NumberS - a serial number normally pertains to one specific unit, but your qty-fields imply more than one physical unit as one "item"

PropertyType? Give an example. Condo/detached housing? Or what?

The setup is not clear, as Pat noted, because there seem to be elementa of inventory keeping mixed together with ... I am not really sure what.

You say custodianS of an item, but your structure only links one custodian to one item ..

Also, are there never more than one "item" with the same name, manufacture, make etc ? Your current structure requires all that filled in for each, and does not square with qty-fields, that imply you have an inventory somewhere, so the same items can be issued later (same/different serial no?)

What is the workflow for one "item" ?

 

[VilaRestal]

Not TMI. Very helpful. It looks good to me except:

Madel and QtyOnHnad

Spell them right now or forever remember to spell them wrong in future.

And what spike said :s

I wonder if perhaps Qty would only apply to 'items' that don't have serial numbers. Otherwise quantity must always be 1. If that's the case then the structure is ok (could perhaps be better but keeping it simple is important too). You just have to have validation on the forms to ensure that rule is followed.

Having a separate model table might be an improvement and reduce duplication. That would contain the fields Manufacturer, Make, Model, PropertyTypeID and your items table would replace those fields with ModelID. But that depends how much models will be duplicated in that table. If very rarely then having to put records into a separate table first could be overkill.

It is possible to over-normalize a database. Especially one that's only going to be used by the person who designed it.

 

[JSimoncelli]

Thanks All, I am sorry for any “face planting” done on my behalf.

To answer a top level question, yes it is a complex and convoluted process. Part of the problem is I am dealing with the government and some of their processes don’t lend themselves to straight forward answers.

I will try to answer your question the best I can. I am trying to design a solution not only for my use but to provide it to others. If I were the only intended user it would not be much of a problem because we (my company) really only obtain cretin kinds of equipment.

Let me give some examples, it is perfectly acceptable to put in a purchase order (PO) for 5ea Dell Optiplex 970 desktop computers 5ea 21” Dell LCD monitors 5ea APC UPS’s all on the same PO and paid for using contract money. Once received, my preference would be to input each piece of equipment in the database as its own record. Another person could put them in as three systems, 1 computer 1 monitor and 1 UPS and record the serial numbers in a notes field and have only 3 records. Yet another person could put the 3 computers in as 1 record (the monitors and UPS the same way) again annotating the serial numbers in the notes. This is why some of the fields are the way they are, like the quantity fields.

@Vila, thanks I will correct the spelling error. I would have found them eventually…..

I have been trying to get my head wrapped around the Norma Forms process. I agree some of the fields should be in a separate table. For example ItemName could use a name change to ItemType as the Item field on the various forms used (the government paper forms not forms in Access), refers to a general name like Hardware, Software or Furniture. Any one of those types would show up multiple in the table as it is now, a clear violation of 1NF. That can be said of several of the fields.
Let me take another pass at this, I will post it tomorrow as I am out the door for today..

Once again Thanks all for your help and patients

John

 

[Pat Hartman]

I actually created a similar system a lifetime ago. I would break the item table into two parts. One would be a product table and would contain the name, model, manufacturer, etc. Whatever occurs ONCE for items like it. If you actually keep items by quantity rather than 1, you could add a flag to this record to indicate whether the product was "seralized" or not. Serialized products contain null for a quantity because they have subordinate records that must be counted. Non-seralized products could have an OnHandQuantity. I don't know what "issued" means in this context. There is still lots of murk here. The government likes all its beans to have asset tags and be indivually accounted for but there are limits. Where do you stop? Side chairs, Coat Racks Staplers?
Then the Item table would contain specific instances of each item and so would include only those fields which were (or could be) unique for each individual item such as tag#, serial number, InServiceDate, OutOfServiceDate, Location, IssuedTo, etc.

 

[JSimoncelli]

Pat,
If you think it would help, I actually have a working Government Furnished Equipment database now. I created this database before I started taking a hard look at database development (outside of Access). This database was created before I started looking into Normal Forms and normalization and it will be evident when you see it. This is the reason for this exercise I would like to take what I created and make the necessary changes. I can make a copy with factious data and post it here.
John

 

[JSimoncelli]

All,
As stated above attached is the working Government Property database. There are parts of the database I did not talk about before, like the Audit part. There is a requirement to maintain audit history.

The way I have implemented the auditing is as follows: Create an audit form by selecting the Audit Inventory Form, once created print out the form. This form is a simple listing of all “Active” property. The form contains enough information to complete the inventory. You complete the inventory by whatever means necessary annotating the results in the “Audit Notes:” portion. Once completed you go back to the main page and select the “Add Audit Inventory Information” button. Using the GFE_ID from each you enter the date and results, the bottom portion of the form displays the corresponding information based on the GFE_ID input. This information is write protected and is used to verify your inputting data for the correct record. You can view the audit history by selecting the “Audit History Report” from the main page. This report produces a listing of all of the property and their audit history. The audit instances are contained in a sub form and are a continuing list of each audit and can grow as you add new audits.

The remaining parts of the database are made up of different queries, forms and report I created for specific purposes.

As I said in my previous post, if you look at the table structure it does not conform to 1NF nor has very good normalization.

John

** I could not upload the database, it is to large. I have removed most of the records but it is still 7.5Mb.

 

[JSimoncelli]

All,

Sorry about not being able to upload the original database. I can send it via email if you are interested.

Also attached is the .pdf of my second pass at normalizing.

John

 

[Pat Hartman]

PropertyDetailID does not belong in the Property table. The PropertyID belongs in the detail table. The location belongs in the detail record since you need to identify the specific location of each item. I'm not sure what the purpose of custodian is so I don't know where it goes. If you have one custodian for all Eames Chairs, then it belongs where it is but if the custodian could change so that I have one custodian for my chair and you have a different one for your chair, then it goes in the detail table.

I can't do anything except give you general guidence because I don't know what the specifications are for the system. I can comment on some things based on years of business knowledge and working with similar applications but there are no hard and fast rules. If there were, we would have one payroll system and every company in the country would use it.

 

[JSimoncelli]

Pat,

Attached is relationship #4 and I have included the section from the FAR clause that identifies the minimum information required to be maintained by the property manager. As per you advice I have put all of the things I believe belong with the PropertyDetail table I have renamed a couple of the items to better identify their purpose.

I will try to give more information to better help on the specification. As you can see from the FAR clause section, the property manager (i.e. me) is required to maintain cretin information. To better help manage the program I have added some additional information. This additional information is based on additional FAR clause requirements for tracking such as disposition.

As you may remember from my previous posts, a piece of equipment/property can be acquired directly from the customer (in the case of me being the prime the government) or purchased using contract money. Either way there is an acquisition cost associated with the transaction, and the equipment/property is considered Government Furnished Equipment/Property (GFE/P). Because items can be tracked differently from organization to organization some fields may not be populated. For example some items may not have a serial number or and computer, monitor and UPS could be tracked as a system with one property tag number or as separate items each with their own property tag number. Welcome to my world.

Most of the tables/fields are self explanatory; I will go over the ones I believe need further explanation.
The ContractorPO field in the Property table identifies the purchase order number used by the contract if they purchased the items using contract money, the actual PO could contain several items. The Cost field in the Property table identifies the actual cost of the item, it could be a line item cost form a PO or the cost listed in the paperwork when acquired directly from the customer. The quantity fields UnitOfIssue, Qty, QtyIssued and QtyOnHand are required in the case of multiple items being tacked under the same property tag.

If you notice I have not identified how the Property and PropertyDetail tables are linked. Based on what I know (and I may be wrong) I would put the PropTag (PK) as a (FK) in the PropertyDetail table and link them as a one to one relationship.

Or…. Would it be better in this case to simply have one Property table and the related Contract, Customer, Type, Location and Custodian tables linked to it (see attached relationship5).

John

 

[Pat Hartman]

If you notice I have not identified how the Property and PropertyDetail tables are linked. Based on what I know (and I may be wrong) I would put the PropTag (PK) as a (FK) in the PropertyDetail table and link them as a one to one relationship.

Yes. But, I think you still don't have the columns in the correct table. Take a look at each column in the details table and ask yourself - does this attribute apply to this class of property or to this specific instance? Things like model and manufacturer apply to the property but serial number and in service date apply to the specific instance of a piece of property.