Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Question Unanswered: Linked fields and referential integrity.

    I am trying to build a little database for tracking client routers.. Amongst other things, a router can have multiple IP addresses assigned, as well a netmask to those IP's.. I want to build a table that holds netmasks, and then in the router table have several fields, eth0netmask and eth1netmask.. Now, I want to be able to have both eth0netmask and eth1netmask fields pull their respective masks from the common tables of netmasks.. But when I try to link both fields in the router table to the netmask field in the netmasks table, and enforce referential integrity, Access yells at me...

    Can anyone explain why?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This design would typically be considered a violation of first normal form (although admittedly a slightly contentious one). These columns (the ones called eth0netmask, eth1netmask, ...ethNnetmask) are known as repeating groups and are generally considered to be a violation of the Atomic principle of relvar attributes. Have a nose here: Fundamentals of Relational Database Design -- r937.com

    The solution is simple. You have many masks. You have many routers. Your relationship is many-many. You need an association table to represent this relationship (relational databases represent many-many as two 1-many relationships).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rereading the link, it uses the term Linking tables not Association tables. Same thing. It does mention repeating groups though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2010
    Posts
    16
    Thanks for the reply, and the link.. I am giving it a read now, and thus far it is quite informative. I think the solution you recommend is what Access calls junction tables, at least that is what I can gather thus far... I will take a look into using this method.

  5. #5
    Join Date
    Mar 2010
    Posts
    16
    Ahh, in reading more deeply, and slowly, I think I understand what needs to be done.

    I can create a table for netmasks, and a table to track the netmask according to ethNNetmask, and then use a composite key in the router table to reference the row in the netmask tracking table...

    Does that make sense?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nearly.

    routers{router_id, router_columns}
    router_columns is a place holder for all your router information. router_id should just be whatever your current primary key is.
    netmasks{netmask}
    This is your available netmasks.
    router_netmasks{router_id, netmask}
    Composite primary key for the junction table - a router can have multiple netmasks and a netmasks can apply to multiple routers.

    Make sense?
    One little thing worth noting - IPs etc. are a wee bit contentious about how best to store them i.e. store all bytes in one column or a byte each in four columns. You might want to look at the pros and cons of each before continuing too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2010
    Posts
    16
    Not only did it make sense, but it tipped me off on how better to do something else that is related as well!! I learned a new concept, as my knowledge is only a step above flat databases.. So not only did you help solve the problem, but I actually learned something... Thanks!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Splendid stuff!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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