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...
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).
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.
router_columns is a place holder for all your router information. router_id should just be whatever your current primary key is.
This is your available netmasks.
Composite primary key for the junction table - a router can have multiple netmasks and a netmasks can apply to multiple routers.
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.
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!