Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Question Unanswered: Normalization Question - Asset Tracking Database

    Hi all, this is my first time posting here, but definitely not the first time I have found answers on this site!

    I am working on creating an IT asset tracking database for our growing retail chain. We have evaluated several different services, but none of them quite satisfy all our needs - so we are going the custom route. Eventually, we plan on using a SQL database and a web front-end, but for now, Access is the quickest way to get something up and running.

    We are looking to track not only assets that are in our stores, but also assets being used by employees. These are the tables that I have currently:
    • Assets (model, serial number, etc)
    • Locations (list of stores)
    • Employees (list of all employees)


    In my initial database design, we were just tracking store assets. I had my Assets table and my Stores table, along with an AssetAssignment Table containing the foreign keys from both.

    If I want to be able to assign an asset to either a store OR an employee, what would be the best way to go about doing this? Should I create a StoreAssignments table and an EmployeeAssignments table? If I do that, would it make it more complicated when trying to run reports/queries for all assets?

    I have played around with Access and SQL database for many years, but never anything too advanced. I am definitely not a DBA!

    Any suggestions you can provide would be much appreciated. I am sure I will have more questions to follow


    EDIT: I just had a new idea, which might be completely the wrong way to go. Should I create some sort of Entity table, in which every location and employee has an EntityID. I could then assign the asset to the Entity, rather than directly to an employee or store? Does that make any sense. Is that the right way to go - or the complete opposite direction?
    Last edited by fwx27; 02-14-17 at 14:22. Reason: New idea...

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    what about some like

    [WhereIsIt]
    WhereIsItID PK
    AssetsID FK
    LocationID FK
    EMPID FK
    ADate Date it Added
    LDate Last Audit Date
    RDate Replacement Date
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    Hello and welcome!

    You might try working backwards in this instance - what information will you need to extract from the database? This can sometimes be a useful guide to table structure. Also, how do the tables that you currently have relate to each other?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •