Unique ID across all tables

[jzwp11]

You will still need to create the query that brings everything together than you can use that as a basis for a cross-tab query. I'm not sure how that will work if some assets will have more properties than others. I have found the cross-tab query to be somewhat limited in how it can present data.

[neo651]

Alright, so what if I make a separate query for each AssetType and write a VBA script that adds a new column to the query everytime a new PropertyType is associated to that AssetType?

[jzwp11]

Alright, so what if I make a separate query for each AssetType and write a VBA script that adds a new column to the query everytime a new PropertyType is associated to that AssetType?

I'm not following you, since the property is associated with an asset not an asset type.

I guess I am a little unclear as to why you need to display all the properties in column rather than using a form/subform design. Using a cross-tab query or something similar will probably not allow you to edit data, so you will need forms with subforms anyways.

More importantly, based on the second section of my description in post #8, I would think that most of what you call properties would be redistributed into other tables. I would recommend taking a closer look at your properties and determine if you have them properly structured.

[neo651]

Sorry, my mistake again. I left out a table. I've also got a junction table that joins AssetTypes and PropertyTypes so I can keep track of which PropertyTypes are associated to which AssetTypes.

It will be far easier for my end users if they can view each AssetType in a consolidated datasheet because that's how their interface has been thusfar.

Lastly, whatever method I need to bring together Assets of each AssetType together with its Properties I would use to also bring them together with whatever other tables I've broken properties out into.

[neo651]

Following normalization rules, all assets should be in 1 table (as records not fields). As to the properties you mention, the same applies. All possible properties should be in a table as records. You then need to bring the asset and its applicable properties together in a third table to properly characterize the asset.

Reviving this thread here. I've been slowly designing a new database for this following your advice. I've just run into a snag though. Above you recommended that all Properties be records in a single table. So I have tblProperties with a column "Property". The problem I'm running into is that there are properties like a computer's brand that are going to be the same 4 or 5 names over and over and then there are properties like serial numbers that have to be unique. But if they're all in the same column how do I make properties like serial numbers unique?

[jzwp11]

The brand or manufacturer of an item is not a property of the item but rather it describes the item.

Going back to your list of "properties"

IP, MAC, SubnetMask, Gateway, SerialNumber, Office, Location, Make, Model, Series, Radio, Username, Password, PurchaseDate, DeploymentDate, RetirementDate, Notes, DNS_Primary, DNS_Secondary, Purpose, Clock_Ghz, RAM, Keycode, FaxNumber, MobileNumber, MobileEquipNumber, ServiceProvider, AE_Title, ModalityType, Circuit, Order, Account, HardwareStatus, VM_Status, ConfigType, ComputerType, MobileDeviceType, MultiFunctionDeviceType, OS_Type

As mentioned previously the dates (purchase, deployment, retirement etc.) are events in the life of the item, not a property of the item. I think hardware status could be addressed via the life events of an item as well.

Username, password and account are not properties of the item, those deal with who owns the device. Along those lines, are the fax and mobile numbers mentioned attributable to the user or the item? I assume they apply to the owner since both can change.


Office and location describe where an item is at a given time, so those are not properties of the item. I think I have already addressed that.

So what really describes an item?

I would say based on your list of "properties", only the manufacturer (HP, Dell, Samsung, Apple, etc.), the model name/number can be classified as describing the item

tblItems
-pkItemID primary key, autonumber
-fkManufacturerID foreign key to a table that holds manufacturers
-txtModelName
-txtModelNumber

Now since each particular item has its own unique serial number that describes a one-to-many relationship, this then is the asset (you might have 10 Iphones of a particular model name/number each with its own serial number). If any of the other "properties" described above are unique to the asset (i.e. follow the serial number) and will not change as the asset passes from owner to owner or change in any other way, then it belongs in tblAsset or a table related to that. If the "properties" change with a change in the event realted to an asset then it belongs in tblAssetEvents or a table related from that. If a "property" follows just the model name/number/manufacturer then it belongs in tblItems or a table related from that table.


tblAsset
-pkAssetID primary key, autonumber
-fkItemID foreign key to tblItems
-txtSerialNo

It is then from this table that you would link the history/events of the asset

tblAssetEvents
-pkAssetEventID primary key, autonumber
-fkAssetID foreign key to tblAsset
-fkEventID foreign key to tblEvents
-dteEvent (date when the event occurred)
-fkPeopleID foreign key to a table that holds info on people; this represents who is responsible for the event associated with the asset. For example, when the item is assigned to a user, you would capture the event (assigned to or deployed) and document the date and the person to which it is assigned or who is now responsible for the asset. You can find the location of the asset by linking the responsible person to a location.

tblEvents
-pkEventID primary key, autonumber
-txtEventName

In the above you would have various records such as purchase, deployment, retirement etc.

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblLocation
-pkLocationID primary key, autonumber
-txtLocationName

Assuming that you have many office in a particular location:

tblOffices
-pkOfficeID primary key, autonumber
-fkLocationID foreign key to tblLocations

Now relate the people to the office where they work

tblPeopleOffices
-pkPeopleOfficeID primary key autonumber
-fkPeopleID foreign key to tblPeople
-fkOfficeID foreign key to tblOffice
-dteEffective (effective date for the person in the office) you will need a history of each change so that you can find assets

[neo651]

Wow. You just dropped a lot of information on me there, but I think I understand what you're saying. I'm excited to try it out. The only problems I can think of though are that not every object will have a serial number and also, it's technically possible that two assets could have the same serial number because those are issued by their respective manufacturerers and are only unique within the company.

[jzwp11]

possible that two assets could have the same serial number because those are issued by their respective manufacturerers and are only unique within the company.

It will be unique because you are still tied back to the manufacturer via link to the item table.

[neo651]

Wouldn't I have to make the ItemID and SerialNo in tblAsset a composite key?

[jzwp11]

No, because the record in the item table carries the manufacturer information which will cover you in the event another manufacturer has the same serial number. Now for those items that do not cover a manufacturer's serial number, you can put in your own unique company serial number if you want.