Confusing Relationships - Addendum

Hi all,

I'm starting a new thread because i did not get response for my latest
posting in the other thread (Confusing relationships), so i started a new
thread. Maybe the post went unnoticed among all the replies etc..

Anyways, heres the expcerpt from the previous thread..
-------
I'm Back,

This time, i have the completed product with me. I thank all of you for
providing invaluable help.

Find it here -- http://members.westnet.com.au/mukund/insurance.zip

This is no way the finished product, but i'm nearly there. I need to do a
fair bit with regards to reports and stuff, also have included a switchboard
(autogenerated) just for the heck of it.

Please review it and let me know...

Thanks
kingnothing
-----------------
PS: There will be no introduction required for people following the old
thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships


Thanks Again..
kingnothing

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200604/1

While this database probably is interesting for you, most of us see several
databases each day. So you might get some specific help with a specific
question, but you may not get any answers just asking people to download and
comment on your database.

BTW, you might find it easier to find and follow your threads by using a
newsreader such as Outlook Express rather than a web interface. Your ISP's
news server is shown on this page:
https://secure1.wn.com.au/autoconfig/default.aspx
or you could use the Microsoft one:
news.microsoft.com

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"kingnothing via AccessMonster.com" u18754@uwe wrote in message
news:5e2d0bbaf4d6d@uwe...
Hi all,

I'm starting a new thread because i did not get response for my latest
posting in the other thread (Confusing relationships), so i started a new
thread. Maybe the post went unnoticed among all the replies etc..

Anyways, heres the expcerpt from the previous thread..
-------
I'm Back,

This time, i have the completed product with me. I thank all of you for
providing invaluable help.

Find it here -- http://members.westnet.com.au/mukund/insurance.zip

This is no way the finished product, but i'm nearly there. I need to do a
fair bit with regards to reports and stuff, also have included a
switchboard
(autogenerated) just for the heck of it.

Please review it and let me know...

Thanks
kingnothing
-----------------
PS: There will be no introduction required for people following the old
thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships

Hi Allen,

Thanks for the reply..

I wasnt at my computer to use NNTP, so i posted on the web.

Anyways, I'm still seeking answer to a particular issue only (Realtionships).
Just wanted to make sure that i had incorporated all the solutions that the
posters had suggested.

Also, According to Vincent Johns and other people who had replied to the
previous thread, i posted the db on the web for review and comments.

Cheers

kingnothing

Allen Browne wrote:
While this database probably is interesting for you, most of us see several
databases each day. So you might get some specific help with a specific
question, but you may not get any answers just asking people to download and
comment on your database.

BTW, you might find it easier to find and follow your threads by using a
newsreader such as Outlook Express rather than a web interface. Your ISP's
news server is shown on this page:
https://secure1.wn.com.au/autoconfig/default.aspx
or you could use the Microsoft one:
news.microsoft.com

Hi all,

[quoted text clipped - 24 lines]
thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships

--
Message posted via http://www.accessmonster.com

I was planning to look at the posted database, but haven't done so yet,
for at least 2 reasons.

(1) I haven't finished an answer to someone else, so that has
priority over this one, which didn't ask a specific question.
(2) It's not in Access 2000 format, so to open it for examination,
I'll have to copy it to my laptop computer, which has Access 2003, open
it in Access 2003, save it in Access 2000 format, copy it back, and open
it in Access 2000... but that will take more time than I want to spend
right now. (You might consider saving example copies in Access 2000
format.)

-- Vincent Johns
Please feel free to quote anything I say here.

kingnothing via AccessMonster.com wrote:

Hi Allen,

Thanks for the reply..

I wasnt at my computer to use NNTP, so i posted on the web.

Anyways, I'm still seeking answer to a particular issue only (Realtionships).
Just wanted to make sure that i had incorporated all the solutions that the
posters had suggested.

Also, According to Vincent Johns and other people who had replied to the
previous thread, i posted the db on the web for review and comments.

Cheers

kingnothing

Allen Browne wrote:

While this database probably is interesting for you, most of us see several
databases each day. So you might get some specific help with a specific
question, but you may not get any answers just asking people to download and
comment on your database.

BTW, you might find it easier to find and follow your threads by using a
newsreader such as Outlook Express rather than a web interface. Your ISP's
news server is shown on this page:
https://secure1.wn.com.au/autoconfig/default.aspx
or you could use the Microsoft one:
news.microsoft.com


Hi all,


[quoted text clipped - 24 lines]

thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships

Thats ok Vincent,

Just thought that the post was lost between all the replies...theres no hurry
to reply...

I really appreciate your time and effort to help people. Thats the good thing
about this site..people actually reply, unlike other places (names skipped
intentionally) where the gurus consider it below their status to answer to
newbies...

Cheers,

kingnothing

Vincent Johns wrote:
I was planning to look at the posted database, but haven't done so yet,
for at least 2 reasons.

(1) I haven't finished an answer to someone else, so that has
priority over this one, which didn't ask a specific question.
(2) It's not in Access 2000 format, so to open it for examination,
I'll have to copy it to my laptop computer, which has Access 2003, open
it in Access 2003, save it in Access 2000 format, copy it back, and open
it in Access 2000... but that will take more time than I want to spend
right now. (You might consider saving example copies in Access 2000
format.)

-- Vincent Johns
Please feel free to quote anything I say here.

Hi Allen,

[quoted text clipped - 31 lines]
thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200604/1

I have now copied your file to my other computer (or, at least, to the
flash memory that I hang around my neck like a dog tag, and that's a big
step closer to the laptop) and will look at it when I get a chance.

-- Vincent Johns
Please feel free to quote anything I say here.

kingnothing via AccessMonster.com wrote:

Thats ok Vincent,

Just thought that the post was lost between all the replies...theres no hurry
to reply...

I really appreciate your time and effort to help people. Thats the good thing
about this site..people actually reply, unlike other places (names skipped
intentionally) where the gurus consider it below their status to answer to
newbies...

Cheers,

kingnothing

Vincent Johns wrote:

I was planning to look at the posted database, but haven't done so yet,
for at least 2 reasons.

(1) I haven't finished an answer to someone else, so that has
priority over this one, which didn't ask a specific question.
(2) It's not in Access 2000 format, so to open it for examination,
I'll have to copy it to my laptop computer, which has Access 2003, open
it in Access 2003, save it in Access 2000 format, copy it back, and open
it in Access 2000... but that will take more time than I want to spend
right now. (You might consider saving example copies in Access 2000
format.)

-- Vincent Johns
Please feel free to quote anything I say here.


Hi Allen,


[quoted text clipped - 31 lines]

thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships

No worries, Thanks

kingnothing

Vincent Johns wrote:
I have now copied your file to my other computer (or, at least, to the
flash memory that I hang around my neck like a dog tag, and that's a big
step closer to the laptop) and will look at it when I get a chance.

-- Vincent Johns
Please feel free to quote anything I say here.

Thats ok Vincent,

[quoted text clipped - 31 lines]
thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships

--
Message posted via http://www.accessmonster.com

kingnothing,

I had written a few comments in general about your Tables, then
decided that much of what I wanted to say actually related a lot to
individual fields. So, I decided to organize them by field (but, as
you'll see, several of them are suggestions about other Tables that I
think you would find useful). It's kind of a long list, but all the
fields within a Table are listed below that Table's name (except that I
called [Policy Type] just [Type], to make the list a bit shorter).

============================
Table: Address

Field
Type Size
Comments
--------------------------------------

Address_ID
Long Integer 4
Primary key, identifying the mailing
address of one or more [Person]s in the
database.

Address Type
Text 50
What is this? If there are only a few
such types, I suggest putting a list of
them into another Table, and linking to
that Table.

Street Address
Text 50
If you need 50 bytes for [Suburb], are
you sure that 50 will be enough for
this? Street names are often longer
than city names.

Suburb
Text 50

State
Text 50
I suggest either linking to another
Table (such as my suggested [State]
Table) or just stuffing the 3-letter
name here.

Country
Text 50
I suggest either a link or a 2-letter
country abbreviation here, not an
entire name.

Pin Code
Text 50
What use does an [Address] have for a
Personal Identification Number Code?
(Or does "Pin" mean something else,
such as postal code? If so, it wasn't
clear to me; I'd suggest a name like
[PostalCode].)

Contact_ID
Long Integer 4
Link to [Person].

Spouse_ID
Long Integer 4
Link to [Person].

Child_ID
Long Integer 4
This allows at most one contact, at
most one spouse, and at most one child
at each address. I think this is
backwards; I would put an [Address_ID]
field into [Person] instead, or, if you
really need a many-to-many
relationship, have a 3rd Table linking
various persons with various addresses,
perhaps with related information such
as when the person moved there and when
he moved away.


============================
Table: Insurance

Field
Type Size
Comments
--------------------------------------

Insurance_ID
Long Integer 4
Primary key, identifying an insurance
policy.

Policy Number
Text 50
Wouldn't most policy numbers be more
like 15 characters long, instead of 50?

Insurance Company
Text 50
I imagine you deal only with a few
companies, and would want related stuff
like contact information for each of
them. So I suggest that that be put
into an [Insurer] Table, and that this
field link to that Table.

Policy Owner
Text 50
Why is [Insurance].[Policy Owner] a
Text field??? I would make this be a
link (foreign key) to some record in
the [Person] Table.

Life Insured
Text 50
It's not obvious what information you
keep here.

Sum Insured
Currency 8
Is this the maximum amount to be paid
if an event listed in the policy occurs?

Premium
Currency 8
Is this a monthly premium?

Premium Mode
Text 50
As there appear to be only 2 possible
values for this, I suggest moving the
list of choices to another Table and
linking to that Table. Or, you could
make this a Yes/No field, with, for
example, Yes = Annual and No = Monthly
and changing the name to
[AnnualPremium?].

Start Date
Date/Time 8
Is this the date at which premiums
first become due, or at which coverage
begins? (Maybe those are the same
date.) In any event, you might want to
somewhere clarify exactly what this
means.

Beneficiaries
Text 50
If this is intended to be the name of
some human being, I suggest using a
link to [Person] or to a Table that
links to [Person]. Otherwise, it's not
obvious what this is supposed to mean.

Renewal Date
Date/Time 8

Comission Upfront
Currency 8

Comission Upfront Percent
Long Integer 4
Must this really be an integer? I
suggest using some data type, such as
Decimal, that allows fractions.

Comission Ongoing
Currency 8
I assume that this is well defined in
your system. Somewhere in your
documentation you should include a
detailed definition, I think.

Comission Ongoing Percent
Long Integer 4
I suggest a data type that permits
fractions.

Monthly Benefit
Currency 8
Can this ever change? If so, how will
you account for that? I think you'd
need to record the date and new value,
each time a change occurs.

Policy Name
Text 50
You already have an [Insurance].[Policy
Number] field. Does this one duplicate
its functions? If so, I suggest
deleting one.

Policy Detail 1
Text 60
If there are only a few possible values
for this, I suggest putting the
possibilities into another Table and
linking to it.

Policy Detail 2
Text 60
How does this differ from
[Insurance].[Policy Detail 1]? If, as
I suspect, there is no essential
difference in data type, you have here
a "repeating group", which can lead to
lots of headaches in database
maintenance. I suggest moving all 5 of
these fields to another Table, and
linking them to this policy via the
value in the [Insurance].[Insurance_ID]
field.

Policy Detail 3
Text 60
(Repeating group, I assume. If not,
its name is very misleading, and I
suggest changing it.)

Policy Detail 4
Text 60
(repeating group)

Policy Detail 5
Text 60
(repeating group)

Contact_ID
Long Integer 4
Is this a reference to the person whose
injury (e.g.) is the event described in
the policy? Or else is it the person
responsible for paying the premiums?
Or are they always the same person (I
assume not)? A more suggestive name
than "Contact" (such as "Insured")
might clarify this, though if "Contact"
is the designation used in the
insurance policy contract, I guess I
would follow the language used in the
contract.

Spouse_ID
Long Integer 4
I notice that you include a "Spouse"
but no "Beneficiary". If the spouse is
not the beneficiary, why do you need to
record any information about the
spouse? If this field is intended to
identify a beneficiary, then I suggest
that its name be changed to reflect its
function.

Child_ID
Long Integer 4
It appears that, to insure more than
one child, one must acquire a separate
policy for each one. Is that also
necessary if one wishes to cover both a
child and a spouse? The design doesn't
preclude covering both child and spouse.


============================
Table: Misc

Field
Type Size
Comments
--------------------------------------

mSuburb
Text 50
Unless they're re-used frequently,
these values might just as well be
expressed as ordinary text inside the
[Address].[Suburb] field.

mID
Long Integer 4
The [Misc] Table looks like an effort
to assign numbers to names, perhaps in
an effort to save space. I suggest
that using a separate Table for each of
these fields, with a suitable Table
name and primary key, would make the
design easier to understand and
remember. For example, see my (new,
unused) Table of [States].

mState
Text 3
It may be a waste of effort to look
these up, instead of just storing the 3-
letter name in a field.

mCountry
Text 50
ICANN has defined 2-letter names for
lots of countries and similar regions;
you might want to include a Table
containing those. You could use the 2-
letter name as its primary key.

mMarriageStatus
Text 50
I suggest putting these into a separate
Table. Incidentally, how does this
relate to insurance? You might want to
add fields that you can use to limit
the types of insurance a person with a
given marriage status can have.

mAdressType
Text 50
This looks like a duplication of
[Address].[Address Type].

mTitle
Text 50
I suggest putting these into a separate
Table.

mSex
Text 50
I suggest putting these into a separate
Table, perhaps with a primary key whose
values are "M" or "F".

mPremium Mode
Text 10
I suggest putting these into a separate
Table, perhaps with a primary key whose
values are "Y" or "M".


============================
Table: Person

Field
Type Size
Comments
--------------------------------------

Person_ID
Long Integer 4
Primary key.

Title
Text 50
This seems kind of long for typical
titles, unless you're including
royalty. But even so, I suggest moving
the list of choices to another Table
and linking to that Table.

First Name
Text 50

Middle Name
Text 50

Surname
Text 50

Preferred Name
Text 50

Sex
Text 50
You probably don't need to allow 50
characters for this. You might be able
to get by with one (such as "M" or "F")
and allow it to be null if unknown.

DOB
Date/Time 8
I assume this means "date of birth",
but it's not really specified
anywhere. At least the Description
field in Table Design View should
specify what it means.

Annual Salary
Text 50
Can the amount paid by the policy
depend on this? (Otherwise, what's the
point of maintaining it?) Since it's
likely to change, I suggest including a
field identifying the date when this
was last updated.

Smoking Status
Yes/No 1
Does this need to be updated at times?
Mark Twain claimed that he quit smoking
numerous times. Do you need a date at
which this was effective? Or is its
value "Yes" if the insured person has
ever smoked at least one cigarette?

Marital Status
Text 50
There can't be very many possible
values for this, I think. I suggest
using a link to another Table.

Mobile Phone
Text 50
This and the other phone numbers are
short enough that, even though they
form a repeating group (and are
therefore candidates for being moved to
a Table of telephone numbers), it
probably won't cause a lot of trouble
to just leave them here. But who uses
a 50-digit phone number?

Business Phone
Text 50

Home Phone
Text 50

Fax
Text 50

Email
Text 50

Notes
Memo 0
OK, but if the memo becomes really
long, you might want to include a
reference to a paper file.

Mailing List
Yes/No 1
If this is "Yes", what does that mean?
That it's OK to send advertising
materials to this person? You might
want to change the name to suggest what
kind of list this is.

Position
Text 50
Is this a job title? Where do you need
to use it? You might include this in
the [Address] Table as part of the
mailing address, if that's where you
need to send notices.

Company
Text 50
Is this an employer's name? This looks
like something that belongs in
[Address].

Spouse_ID
Long Integer 4

Child_ID
Long Integer 4
Your [Persons] Table allows AT MOST one
child (probably too restrictive, unless
you're in China). You can never record
more than one child with this design.


============================
Table: State

Field
Type Size
Comments
--------------------------------------

State
Text 3
Primary key, identifying a state.
This Table is an example of how I would
organize the state names, instead of
putting them in [Misc]. Actually,
since these names are pretty short, I
just used these values as the primary
key, instead of using a 4-byte number.

TaxRate
Decimal 16
This is just an example of the kind of
information that you might include that
would relate to a state. (I don't even
know if these states impose ad valorem
taxes.)


============================
Table: Type

Field
Type Size
Comments
--------------------------------------

Insurance Type ID
Long Integer 4
Primary key to [Insurance Type] Table,
identifying a type of standard policy.

Insurance Type
Text 55
Some of the types in [Insurance Type]
seem to overlap more than others. Do
you want to allow someone to have both
"Income Protection: Accident Benefit:
Upto 65" and "Income Protection:
Accident Benefit: Upto 60"? But it might
make sense to have both one of these
and a "Children's Trauma" policy. The
insurance company probably already has
a system for categorizing policies; you
might be able to use that same system.

Well, that's it (for now). As I assume you're aware, there are many
ways to organize a relational database, but some involve more work than
others. The purpose behind my comments, generally, is to reduce the
needed work. Good luck.

-- Vincent Johns
Please feel free to quote anything I say here.

kingnothing via AccessMonster.com wrote:

No worries, Thanks

kingnothing

Vincent Johns wrote:

I have now copied your file to my other computer (or, at least, to the
flash memory that I hang around my neck like a dog tag, and that's a big
step closer to the laptop) and will look at it when I get a chance.

-- Vincent Johns
Please feel free to quote anything I say here.


Thats ok Vincent,


[quoted text clipped - 31 lines]

thread...but for others, please refer --
http://www.accessmonster.com/Uwe/For...-relationships