Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2015
    Provided Answers: 1

    Unanswered: Am I on the right track in designing this database?

    Hello everyone,

    I'm just learning how to create an Access database (really not much experience in databases at all) and I'm hoping that someone can take a look at my screenshot here and tell me if it appears that I'm on the right track with my design, or if I'm entirely missing the picture in how a database ought to be setup from the tables standpoint.

    I've been trying to self-teach myself a bit here. So far, the general concepts I've found and am trying to implement is that tables shouldn't contain data already found in other tables, although there may be one or two fields that correspond (Primary key and foreign keys).

    I believe I know basically what kind of data I need and I've tried to set it up in it's own tables to separate it out in an efficient manner, however i'm having some trouble with the relationships and understanding how they work. For example, I seem to be able to enter incorrect data in a field even though the field it's linked to has validation constraints on it.

    I don't know if this screenshot is a good way to ask my question, but basically, from what you can infer from it, can you tell me if it looks like I've got the right idea on this at all?

    Basically, it's an asset database that is intended to track the following:
    • General Equipment
    • What room the equipment is located in
    • What employee has the equipment
    • What phone extension the employee has
    • Other misc. phone/equipment/employee details

    Click image for larger version. 

Name:	TableRelationships.PNG 
Views:	10 
Size:	38.8 KB 
ID:	16440

    Thank you for your time and answers.
    Last edited by JDDellGuy; 07-11-15 at 15:31. Reason: Adjusted image location

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 32
    I don't see why an inventory item is tied to a phone#.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    can an employee have more than one location and or more than one phone number
    can you have more than one item form a single manufacturer, iuf so then probably there should be a separate entity for manufacturer
    can the same product be supplied by different reseller / vendors
    device category screams out as a separate entity
    I think you are confusing a product with inventory. a product is a product, there may be 0,1 or more of that product but they may be in different locations. ferisntance a company may have a telepjone system using the same handsets. so the company has handsets but also needs to know where each individual handset is. so Id expect something that defines types of stock items (eg a Cisco IP handset), and then another table that describes specific instances of those items (eg handset X in stores, handset y in reception and so on)
    use relational links where possible, enforce data integrity through relational links
    is it possible that two manufacturers could separately produce items which happen to have the same serial number
    consider using the sub/supertype model to handle category specific features. or, and I hate to even suggest it consider the EAV for non ciriti9cal information
    if this is general inventory then is it possible that you may have 'stuff' which isn't connected on networks and so may not have n ip address

    be consistent in your naming of columns and tables. either use CamelCase (SerialNo) or Underscore (serial_no), but don't use spaces to separate words. if you must have spaces then set the caption property as part of the tabel design NOT the column name itself
    be wary of using reserved words or symbols, although Access does a pretty good job of resolving such probelms its not clever and at some stage it will bite you badly.
    you refer to a table called room numbers but have a (presumably) FK called location... consistency. by all means call tables by their plurals (eg Locations) but where the column is used call it location.

    do you need to refien your location (ie is a room number sufficient or should it also define buildings, areas etc... not every item of inventory is neccessarily in a room or building for that mater
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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