Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110

    Unique ID across all tables


    I have a database that tracks objects in our company (i.e. Cell Phones, Computers, Servers, Software, Warranties, etc). In total we have 33 different objects we track. But now I need to have a unique ID across all objects. I thought about making a tblObjectIDs with one column, ObjectID, and then making an ObjectID column in all the other tables. The problem is that there is a 32 relationship limit per table and I've already exceeded that before I've even gotten started and who knows how many more tables I'll need to add in the future.

    My other idea was to consolidate all tables into one and just having a column that specifies what type of Object it is. The problem is that there is such a wide variety of properties each table has that I'll exceed the limit of how many columns you can have in a table.

    What's the best design approach for this situation?

    Thank you.

  2. #2
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    You may want to have a look at the "Assets" template for Access 2010.

    You could have an Assets table for each object, and an AssetDetails record for each asset property.

  3. #3
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    That's basically what my second idea was. But like I said, the problem there is that the AssetDetails table, to accomodate all the various properties of all the Assets, would exceed the maximum number of columns allowed in a table.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

    By the way can you provide a list of those properties?

  5. #5
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    I like that design concept and I've been trying to work it out, but I run into a problem. The properties are of a variety of data types. If I had a single table for all properties I would have to have a separate column for each data type and this breaks the rules of normalization because the table will be mostly empty space.

  6. #6
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    Sorry, also, here's the 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

  7. #7
    pdebaets is offline Competent Performer
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Jul 2010
    Location
    Los Angeles
    Posts
    235
    Here's an example AssetDetails table:

    Table: AssetDetails
    PK: AssetDetailID
    FK: AssetID
    AssetPropertyName
    AssetPropertyValue

    Then your data will look something like this (for AssetID=1 and AssetID=2):

    AssetDetailID, AssetID, AssetPropertyName, AssetPropertyValue
    1, 1, IP, 168.92.0.3
    2, 1, MAC, f0-ab-c3-c4
    3, 1, SubnetMask, 255.255.255.0
    4, 2, ServiceProvider, Verizon
    5, 2, ComputerType, laptop
    6, 2, UserName, Harry
    etc...

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Just some thoughts...

    In the property table, have a field that identifies the data type of the output.

    tblProperties
    -pkPropertyID primary key, autonumber
    -txtProperty
    -fkOutputDataTypeID foreign key to tblOutputDataTypes

    tblOutputDataTypes (each datatype would be a record in this table)
    -pkOutputDataTypeID
    -txtOutputDataType

    Then in the junction table between assets and properties you can do either of a couple of things

    1.
    tblAssetProperties
    -pkAssetPropertyID primary key autonumber
    -fkAssetID foreign key to tblAssets
    -fkPropertyID foreign key to tblProperties
    -textfield to hold value (you can use the output datatypes to validate the user input in the form at data entry to make sure the text value can be converted to the appropriate data type when needed later.)


    2.

    tblAssetProperties
    -pkAssetPropertyID primary key autonumber
    -fkAssetID foreign key to tblAssets
    -fkPropertyID foreign key to tblProperties
    -txtValue (a text field to hold the value)
    -dteValue (a date field to hold the value)
    -lngValue (a long number field to hold the value)
    (not sure how many or which types of datatypes you need)

    On the form, you would show the correct value control based on the output datatype of the property.

    Just saw your new post--changes everything above

    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
    In your list above the fields purchase date, deployment date and retirement date refer to activities related to the asset--not a property of the asset. You can have many activities (purchase, deployment, retirement) relative to the asset (one-to-many relationship) which requires a separate but related table.

    tblAssetActivities
    -pkAssetActID primary key, autonumber
    -fkActivityID foreign key to tblActivities
    -dteActivity (date of the activity)

    tblActivities (holds records such as purchase, deploy, retire)
    -pkActivityID primary key, autonumber
    -txtActivity

    The fields SerialNumber, Make, Model, Series and some of the other fields look to be specific to the asset, so shouldn't they go in the asset table?


    The fields: Office, Location suggest where the asset is located. If the asset can be moved then that suggests that an asset can have many locations, thus a one-to-many relationship which requires a separate table to track

    tblAssetLocations
    -pkAssetLocID primary key, autonumber
    -fkAssetID foreign key to tblAssets
    -fkLocationID foreign ket to tblLocations (all locations stored in tblLocations as records)
    -dteEffective effective date of when the asset was at the indicated location


    I'm guessing that an asset can have many service providers over time; another one-to-many relationship, thus a separate table would be needed to track.

    I am guessing that these are just types of assets: ComputerType, MobileDeviceType, MultiFunctionDeviceType

    tblDeviceTypes (computer, mobile, multifunction)
    -pkDevicceTypeID primary key autonumber
    -txtDeviceType

    And then you would reference fkDeviceTypeID in the asset table. Now if an asset can be of many types then you need a junction table

    tblAssetDeviceType
    -pkAssetDeviceTypeID primary key, autonumber
    -fkAssetID foreign key to tblAssets
    -fkDeviceTypeID foreign key to tblDeviceTypes

  9. #9
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    This is all sounds good and the design is going well so far except I hit another snag. I failed to mention that I'm not just tracking assets but also things like Offices, Warranties, Personnel and Software licenses. I need an unique ID across all of my objects so they need to be in the same table with the things like the assets but these Objects are also properties of other Objects. So, for example, Personnel (User) and Warranties can both be properties of a Desktop which are all Objects in the same table. How do I handle this?

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I need an unique ID across all of my objects so they need to be in the same table
    Not necessarily, it depends on the relationship between the entities.

    You would have a table for personnel, now can a person be responsible for many assets? Can an asset belong to many people over time? If so, you have a many-to-many relationship which cannot be represented by having all of the uniqueIDs all in one table.

    For the offices, I assume you have many people at an office, so that describes a one-to-many relationship. I also assume that people can move between offices, so a person can be associated with more than one office over a period of time. Again, this describes a many-to-many relationship between offices and people.

    Once you build the relationships correctly, you would bring the related information together using a query or you can use forms and subforms for data entry/editing.

  11. #11
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    Alright. I think I can manage that. But then how would I bring the Assets together with their AssetProperties into a Datasheet Form?

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Once you have your tables created, you would establish the relationships in the Relationship Window. From there you would build a query to bring the data together. You would then use that query as the record source for your form.

  13. #13
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    I understand the fundamental ideas there. But I don't know how to actually do it. How do I make a query where the columns are the AssetID from the tblAssets and the other columns are all the AssetProperties from the tblAssetProperties?

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Create-->Query design-->select the tables you want from the list. Then select the fields from the respective tables that you want displayed as part of the query results. Run the query to see if it what you want; if so, save the query. Highlight the query name in the navigation pane then Create-->Form

  15. #15
    neo651 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    110
    I don't think it's that simple though.

    Here's my design so far.

    tblAssetTypes has rows AssetTypeID (pk) and AssetTypeName
    tblAssets has rows AssetID (pk) and AssetTypeID (fk)
    tblPropertyTypes has rows PropertyTypeID (pk) and PropertyTypeName
    tblProperties has rows PropertyID (pk), PropertyTypeID (fk), AssetID (fk), and PropertyValue

    I need to create a datasheet form where the rows are all the Assets of a single AssetType and the columns are all the PropertyTypes associated to that AssetType.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unique values
    By helpaccess in forum Queries
    Replies: 3
    Last Post: 09-19-2011, 03:46 PM
  2. Replies: 4
    Last Post: 08-01-2011, 04:24 PM
  3. composite unique key
    By shital in forum Access
    Replies: 1
    Last Post: 07-09-2010, 08:07 AM
  4. how to set a unique ID
    By archie in forum Access
    Replies: 1
    Last Post: 09-08-2009, 04:28 AM
  5. Unique Record Identifier for MS SQL Server Tables
    By MGSP in forum Import/Export Data
    Replies: 2
    Last Post: 01-29-2006, 03:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums