One-to-Many and Many-to-One are the same kind of relationship... which you
might talk about depends on which table you regard as primary. My tendency
is always to use One-to-Many as, almost always, the "one" side is "primary".
In your case... the insured_person table is the "one" side, and the
insurance_policy table records are each related to a single insured_person,
so they are the many side, and should contain the key of the insured_person
to which the record is related as a "foreign key". No change to the design
of the tables will be required if you add additional policy types -- it
would, and would be a nightmare, if you tried to put the foreign key
field(s) in the one side of the relationship.
This type of relationship works very well. For example, if you wanted a
report of Insured Persons and the policies they own, you could
1. Join the insured_persons and insurance_policy tables on the primary key
of the insured_persons table and the foreign key to the insured_persons
table of the insurance_policy table, and voila you get one record per
insured-and-policy matchup -- very easy to report,
OR, you could
1. Create the report on a query against the insured_persons table (or the
table itself), include a subreport control, and embed a report on the
insurance_policy table, using the primary key of the insured_person as the
LinkMasterFields and the foreign key of the insurance_policy as the
LinkChildFields property of the subreport control.
I have often used Visio to _document_ my table design, but never to design
it. I've imported my tables into Visio to get an automatic drawing, but
never done the reverse. Depending on the version of Visio and Access, you
may have to use "ODBC" and connect your own relationships if you import
them. Sorry I can't be of help. (I've been doing this stuff so long that my
"ER modeling" is done mentally, not with software.)
Larry Linson
Microsoft Access MVP
"Kevin Ramakers" <ca**********@home.nl> wrote in message
news:1Z************@news.uswest.net...
Dear Mr. Linson,
What you explain is probally what I need to do. However I am not a DBA,
so I have some questions. The insured client table should only contain
one record per insured client. Now the insured client can have a maximum
of 4 insurances (because I only used four different insurances, every
insurance has its own table, I thought that one table insurance_type
would give me trouble later on plus normalization right!!). So in the
insurance_policy table one specific insured_client can occur many times
(4X in this case, but well when I like to expand to 5 or more insurances
this should be possible without hassle). The Insurance_Policy table
should contain attributes on the agent/intermediarie, insurance(type),
insured_client. Basically the Insurance_policy should consist of
attributes from other tables with exceptions a uniqe policy number which
will serve as PK. Also can I say now that an Insurance Policy has one
insured client, but an insured client has more insurance policies. And
is this then one too many or many too one(or is this the same)?
Basically can this work or am I completely off here.
Thanks a million for your attention.
PS know off a site where I can find in depth tutorials on how to use ms
visio to design my ER diagram properly and to tell me how to export it
into a real rdbms.
and I know I ask a lot!!!
*** Sent via Developersdex http://www.developersdex.com ***
