> i've created a database to handle an insurance form which > deals with the insurance of money. it has a number of > different tables such as 'cust', which lists details of > customers, 'prem', which lists details of where the money is > stored, and 'safe' which lists details of the safes in which > the money is stored. > > the tables 'safe' and 'prem' relate to each other, in that a > premises may have one or more safes, and one type of safe may > be used in more than one premises. another way of putting it > is that each safe could have 1 or more premises that match to > it & each premises could have 1 or more safes that match to > it, with a maximum number of 3 safes or premises. what > structure should i use for the link between the two tables? You have what is called a "many-to-many" relationship. There's no way to directly represent a many-to-many relationship in a relational database, so you'll need to create a table for this purpose. This table, called a linking table or an intersection table, should contain the primary key columns from each of the other two tables, and should be related to each of those tables using a one-to-many relationship; one record from each table can have many matches within the linking table. As a primary key, the linking table should use the two foreign keys together - a composite primary key. In Access, you can create these relationships using the Relationship window, once you've created the linking table. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
