My problem is described here in detail
any help, suggestions are very welcome
K.R
any help, suggestions are very welcome
K.R
-
-
Re: RDBMS
"cartoonsma rt" <cartoonsmart@h ome.nl> wrote in message
news:1115296046 .267723.34020@o 13g2000cwo.goog legroups.com...[color=blue]
> My problem is described here in detail
> http://members.home.nl/cartoonsmart/
> any help, suggestions are very welcome[/color]
On just a quick glance, it seems the problem is that you need to put the
foreign key to the insured in the policy table, provided there is a record
there for each issued policy, and that it is not just common policy
information for every policy of that type. If the latter, then you will need
a junction table with foreign keys to the insured table and to the policy
table, to handle a "many-to-many" relationship.
Larry Linson
Microsoft Access MVP
-
Re: RDBMS
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_polic y 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_Polic y table
should contain attributes on the agent/intermediarie, insurance(type) ,
insured_client. Basically the Insurance_polic y 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 ***
Comment
-
Re: RDBMS
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_polic y 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_polic y tables on the primary key
of the insured_persons table and the foreign key to the insured_persons
table of the insurance_polic y 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_polic y table, using the primary key of the insured_person as the
LinkMasterField s and the foreign key of the insurance_polic y 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" <cartoonsmart@h ome.nl> wrote in message
news:1Z0fe.1$Oj .279@news.uswes t.net...[color=blue]
>
>
> 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_polic y 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_Polic y table
> should contain attributes on the agent/intermediarie, insurance(type) ,
> insured_client. Basically the Insurance_polic y 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 ***[/color]
Comment
-
Re: RDBMS
Dear Mr Linson,
I took your advice, I hope I kinda got it right. So, well could you be
so kind as to look at my database. Its available for you as an access
mdb file on http://members.home.nl/cartoonsmart
I hope to hear from you again as you have been a big help.
thanks again
Kevin Ramakers
*** Sent via Developersdex http://www.developersdex.com ***
Comment
Comment