confusing relationships

[Vincent Johns]

OK, here's a suggested (alternate) design for your Table structure.
Although I specified enforcing referential integrity for most links, I
did not do so with the [Person] Table, because it interfered with adding
new records.

All the primary keys (the ones identifying the record they're in) have
names consisting of the name of the Table, followed by "_ID". The
foreign keys (those used to refer to some other record) have similar
names, but possibly prefixed by a name suggesting what they contain,
such as [Parent_Person_ID] to refer to [Person_ID].


+----------+
|Person |
| _Client |
1|----------|
.--|Person_ID |
| +----------+
|
| +----------+ +------------+
| |Person | |Insurance |
| | _Child | | _Child | +---------+
| |----------|1 oo|------------| |Policy |
| oo|Person_ID |-----|Insured_ | | _Child |
+---|Parent_ | | Person_ID |1 oo|---------|
| | Person_ID| oo|Insurance_ID|------|Insurance|
| +----------+ .--|Company_ID | oo| _ID |
| : +------------+ .--|Detail_ID|
| : +---------+
| +-----------+ +------------+
| |Person | |Insurance |
| | _Spouse | | _Spouse | +---------+
| |-----------|1 oo|------------| |Policy |
| oo|Person_ID |-----|Insured_ | | _Spouse|
+---|Spouse_ | | Person_ID |1 oo|---------|
| | Person_ID| oo|Insurance_ID|------|Insurance|
| +-----------+ .--|Company_ID | oo| _ID |
| : +------------+ .--|Detail_ID|
| +------------+ : +---------+
| |Insurance |
| | _Client | +------------+
| oo|------------| |Policy |
'---|Insured_ | | _Client |
| Person_ID |1 oo|------------|
oo|Insurance_ID|-----|Insurance_ID|oo
.---|Company_ID | |Detail_ID |---.
| +------------+ +------------+ |
| |
| +----------+ +---------+ |
| |Company | |Detail | |
| 1|----------| |---------|1 |
'---|Company_ID| |Detail_ID|----'
+----------+ +---------+

Note: oo = "many" end of relationship
1 = "one" end of relationship

Example Tables follow, in alphabetical order. (In my own databases, I
define Lookup properties on all my foreign keys, to make the datasheets
easier to read. In this case I thought it might be better to show you
the raw key values stored in the Tables, so you could observe how they
link together.)

Each of the Tables probably needs additional fields.

This Table, as Tom Ellison suggested, lets you list each address just
once, to be shared by everyone living at that address. (Instead of
entering "836 SE 127th St." into each of several records, possibly
mistyping it, you select the number 12245183 from a list. Actually, the
user shouldn't see the number, but rather a list of addresses in a combo
box on a Form, with the option of entering a new address.)

[Address] Table Datasheet view:

Address_ID Address
---------- ------------------
12245183 836 SE 127th St.
155959414 666 Banshee Blvd.
173127785 1234 E. Main St.

The next Table does the same with insurance company names.

[Company] Table Datasheet view:

Company_ID Name
----------- -------
-1459391524 Everest
-78983385 Acme

This Table lists various perils that might be covered; each may appear
in multiple policies.

[Detail] Table Datasheet view:

Detail_ID Type
----------- ----------
940690545 Earthquake
443127896 Fire
-450676459 Flooding
-536392113 Hail
-1066305205 Hair loss
-2146535970 Lightning
-2001082702 Loss of nest
2001901126 Meteor strike
-492063696 Tooth decay
-512829575 Volcano

In the next Table, I couldn't determine what [Policy Owner] might be.
You can determine the contact person by following links. For example,
in a child's policy, [Insured_Person_ID] identifies the child, and that
record's [Parent_Person_ID] link identifies the client who owns the
policy. Or, this field could be a direct link to the owner's record,
but it's usually good to avoid storing redundant information in a Table,
because maintaining it takes unnecessary extra work.

The lines were a bit too wide for email, so I split the Table's listing,
repeating the primary key, [Insurance_ID], to help keep track of the
records. (It appears only once in each record.)

[Insurance] Table Datasheet view:

Insurance_ID Policy Company_ID Policy Life
Number Owner Insured
------------ ------ ----------- ------- -------
-1041598040 882-22 -1459391524 B. Bird No
-543328349 179-33 -78983385 Snuffy No
-242941295 600-28 -78983385 Ernie No
-9603188 882-57 -1459391524 Maria No
852585834 772-63 -1459391524 Oscar Yes
998160080 885-42 -1459391524 Bert No
2058552749 816-80 -1459391524 No

Insurance_ID Insured_Person_ID
------------ -----------------
-1041598040 8940911
-543328349 -1071790618
-242941295 -1737207726
-9603188 -1987616873
852585834 -2135742055
998160080 -2135742055
2058552749 453834372

The next Table includes records for all contacts, spouses, and children,
with links to other information such as address, parent, or spouse.

[Person] Table Datasheet view:

Person_ID Title First Middle Surname Sex
Name Name
----------- ----- ------- ------ ------- ---
-2135742055 Ms Mary A Jones F
-1987616873 Ms June Jones F
-1737207726 Colleen Smith F
-1071790618 Dr George Jones M
8940911 Tracy Q Jones F
453834372 Billy Jones M
1942089013 Mr Sydney Smith M


Person_ID Spouse_ Parent_ IsCon Address_ID
Person_ID Person_ID tact?
----------- ----------- ----------- ----- ----------
-2135742055 -2135742055 0 Yes 155959414
-1987616873 -2135742055 0 No 173127785
-1737207726 0 1942089013 No 12245183
-1071790618 0 Yes 173127785
8940911 0 -2135742055 No 173127785
453834372 0 -2135742055 No 173127785
1942089013 0 0 Yes 12245183

The next Table contains only some links, in each record identifying some
insurance policy and some detail (in my example, the name of a covered
hazard) in that policy.

[Policy] Table Datasheet view:

Policy_ID Insurance_ID Detail_ID
----------- ------------ ------------
-1772889958 -9603188 443127896
-1286666971 -242941295 -512829575
-742185598 852585834 2001901126
-511421815 -1041598040 -536392113
-478125093 852585834 443127896
-218923041 -543328349 -450676459
1000970262 -1041598040 -2001082702
1179632046 2058552749 -1066305205
1191726976 998160080 -492063696
1407383937 998160080 940690545
1450169396 -242941295 -2146535970
1722141597 -9603188 -492063696

Now, you may well ask, what good is it? Is there a way to get a
meaningful list from these Tables full of ugly-looking numbers? Fear
not, it should be easy. For example, the next Query lists all the
hazards in all the policies covering a child of one of the contacts.

Notice that I renamed some of the references to [Person] to reflect just
what kind of person the reference intended to reflect. For example,
[Child].[First Name] actually looks up the [Person].[First Name] field,
but calling it [Child] helps us remember that we're looking at a child's
name, instead of a spouse's name.

[Q_Children's Policies] SQL:

SELECT Contact.[First Name], Contact.Surname,
Child.[First Name], Child.Sex, Address.Address,
Company.Name, Insurance.[Policy Number],
Detail.Type
FROM ((Person AS Child INNER JOIN Address
ON Child.Address_ID = Address.Address_ID)
INNER JOIN Person AS Contact
ON Child.Parent_Person_ID = Contact.Person_ID)
INNER JOIN ((Insurance INNER JOIN Company
ON Insurance.Company_ID = Company.Company_ID)
INNER JOIN (Detail INNER JOIN Policy
ON Detail.Detail_ID = Policy.Detail_ID)
ON (Insurance.Insurance_ID = Policy.Insurance_ID)
AND (Insurance.Insurance_ID = Policy.Insurance_ID)
AND (Insurance.Insurance_ID = Policy.Insurance_ID))
ON (Child.Person_ID = Insurance.Insured_Person_ID)
AND (Child.Person_ID = Insurance.Insured_Person_ID)
AND (Child.Person_ID = Insurance.Insured_Person_ID)
WHERE (((Contact.[IsContact?])=Yes))
ORDER BY Contact.Surname, Child.[First Name];

The results of running this Query look like this (but again I had to
split the output, which was too wide for the page):

[Q_Children's Policies] Query Datasheet View:

Contact. Surname Child. Sex
First Name First Name
---------- ------- ---------- ---
Mary Jones Billy M
Mary Jones Tracy F
Mary Jones Tracy F
Sydney Smith Colleen F
Sydney Smith Colleen F


Address Name Policy Type
Number
---------------- ------- ------ ----------
1234 E. Main St. Everest 816-80 Hair loss
1234 E. Main St. Everest 882-22 Loss of nest
1234 E. Main St. Everest 882-22 Hail
836 SE 127th St. Acme 600-28 Volcano
836 SE 127th St. Acme 600-28 Lightning

Please bear in mind that, though I entered these imitation data by using
Table Datasheet View, your users should use Forms to perform that
function. Using a Form will allow you to check for obvious mistakes
while a user is entering data, so you can help protect the contents of
the Tables from damage. Access provides a Wizard to help you generate a
Form, once your Query is working properly.

There is also a Report Wizard that will help you design a Report so that
you can see a list which suppresses repeating information, such as the
street addresses or the sponsor's names.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hello, kingnothing, I'm back...

At the risk of reiterating my previous comments, I don't agree with your
"apart from...", since maintaining those separate but largely isomorphic
Tables implies that you'll later be essentially REQUIRED to do a lot of
work destroying them (=moving their contents to a well-designed Table),
after you've done work building them, when you could do that right now,
while you're still designing the things and haven't invested lots of
effort into populating them. Several people have given you good advice.
It's possible to do useful work with poorly designed Tables, but it
takes (maybe much) more effort than necessary.

The self-joins that jacksonmacd and maybe others have suggested
shouldn't be that scary, especially if you're working in Query Design
View. You can even rename the extra views of your Table -- just
right-click on the Table in the upper portion, choose Properties, and
enter a new name into the Alias field. It's still a reference to the
same Table, but it looks in Query Design View like a different Table.

You mentioned that you want to keep things simple. That's a good idea;
as mnature suggested, tracking all possible relationships (maybe to a
dozen generations) will take work and maybe be unnecessary. One virtue
of a RDBMS like Access is that it's pretty flexible. You can start with
a simple model, get it working, then as you discover that you need to
model additional information, it's often not difficult to extend your
original design to take care of that. I've never had to throw away an
Access database because of its becoming unmaintainable. (I have,
however, had to throw away Tables and other objects at times... but
that's not quite as drastic.)

Anyway, I have a few minutes, so I'll try to post another message soon
showing you a bit more graphically what others & I are trying to suggest
that you do.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design,
right!! Please look at http://members.westnet.com.au/mukund/rel.gif
and tell if if
there is anything wrong with the way the database is designed (Apart
form the
fact that i will be storing simillar records in different tables)

PS: if you want a clearer picture let me know, i can make another one

Regards,

kingnothing

Dear Kingnothing:

Putting the relationships into the table design is an essential.
There is something called referential integrity.

If you have a one-to-many relationship you must not allow the rows on
the many side to be "orphaned". That means, you cannot delete a row
in the table on the "one" side when there are rows on the "many"
side. You must either delete them, too, or prohibit deleting the
"one". That's the job of referential integrity. Try it! You'll see
what I mean.

So then, without enforcing referential integrity, there is little
likelihood the database will work well later when you write those
queries.

Tom Ellison


Please see inline..


[quoted text clipped - 51 lines]

kingnothing

 

[Guest]

Your contact table would have an AddressID field. You would then have another
table containing all the address information. This would also have an
AddressID field which will be the primary key. You then create a relationship
between the two tables (open the relationships window, add the two tables and
drag one AddressID field on to the other, click "enforce referential
integrity".

Each contact in the same household would have the same AddressID.

Dave

Please see inline..

Dear Kingnothing:

I was toying with the idea of having just a single table of persons.
Self-referencing links could determine relationships (spouse, father,
mother). An attribute of this table could be Contact, a yes/no. Filtering
on this would give you only those currently in your contact table.


I would keep a separate table of addresses. Husband, wife, son, and
daughter could link to the same address record (including phone and other
details if desired) so that, when someone moves, everyone in a family group
would "move" together with only a single change.

This is what i have been told by all the kind souls that are trying to help
me. But what eevryone fails to understand is that i dont know how to do it.
If you can be kind enough and explain the same in a detailed fashin, it would
be very helpful. If you can, please represent it a little graphically for me
to understand.

I was also wondering if i should just leave the relationships out of the
table and just create joins in Queries and make those queries the source for
my forms. Is that how it works??

This is a bare outline of just a portion of the system.

A complete job would be very considerable. But proper table designs are the
foundation of all the work that follows. If you get a bad table design, the
work you build on top of that can be a huge waste.

I realise that and am striving to achieve that perfect database design .

Normalization of tables isn't just some dry set of rules, not to me. It is
fundamental to the proper way of thinking about a database. The rules are
there to guide you to being able to properly analyze, categorize, and
synthesize (and any other "ize") the solution. The rules are natural and
essential ways of thinking.

Tom Ellison

Hi Tom,

[quoted text clipped - 97 lines]

kingnothing

 

[kingnothing via AccessMonster.com]

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

During the course of the day, i will modify my message according to your
suggestion, and see how it goes.

Thanks

kingnothing

OK, here's a suggested (alternate) design for your Table structure.
Although I specified enforcing referential integrity for most links, I
did not do so with the [Person] Table, because it interfered with adding
new records.

All the primary keys (the ones identifying the record they're in) have
names consisting of the name of the Table, followed by "_ID". The
foreign keys (those used to refer to some other record) have similar
names, but possibly prefixed by a name suggesting what they contain,
such as [Parent_Person_ID] to refer to [Person_ID].

+----------+
|Person |
| _Client |
1|----------|
.--|Person_ID |
| +----------+
|
| +----------+ +------------+
| |Person | |Insurance |
| | _Child | | _Child | +---------+
| |----------|1 oo|------------| |Policy |
| oo|Person_ID |-----|Insured_ | | _Child |
+---|Parent_ | | Person_ID |1 oo|---------|
| | Person_ID| oo|Insurance_ID|------|Insurance|
| +----------+ .--|Company_ID | oo| _ID |
| : +------------+ .--|Detail_ID|
| : +---------+
| +-----------+ +------------+
| |Person | |Insurance |
| | _Spouse | | _Spouse | +---------+
| |-----------|1 oo|------------| |Policy |
| oo|Person_ID |-----|Insured_ | | _Spouse|
+---|Spouse_ | | Person_ID |1 oo|---------|
| | Person_ID| oo|Insurance_ID|------|Insurance|
| +-----------+ .--|Company_ID | oo| _ID |
| : +------------+ .--|Detail_ID|
| +------------+ : +---------+
| |Insurance |
| | _Client | +------------+
| oo|------------| |Policy |
'---|Insured_ | | _Client |
| Person_ID |1 oo|------------|
oo|Insurance_ID|-----|Insurance_ID|oo
.---|Company_ID | |Detail_ID |---.
| +------------+ +------------+ |
| |
| +----------+ +---------+ |
| |Company | |Detail | |
| 1|----------| |---------|1 |
'---|Company_ID| |Detail_ID|----'
+----------+ +---------+

Note: oo = "many" end of relationship
1 = "one" end of relationship

Example Tables follow, in alphabetical order. (In my own databases, I
define Lookup properties on all my foreign keys, to make the datasheets
easier to read. In this case I thought it might be better to show you
the raw key values stored in the Tables, so you could observe how they
link together.)

Each of the Tables probably needs additional fields.

This Table, as Tom Ellison suggested, lets you list each address just
once, to be shared by everyone living at that address. (Instead of
entering "836 SE 127th St." into each of several records, possibly
mistyping it, you select the number 12245183 from a list. Actually, the
user shouldn't see the number, but rather a list of addresses in a combo
box on a Form, with the option of entering a new address.)

[Address] Table Datasheet view:

Address_ID Address
---------- ------------------
12245183 836 SE 127th St.
155959414 666 Banshee Blvd.
173127785 1234 E. Main St.

The next Table does the same with insurance company names.

[Company] Table Datasheet view:

Company_ID Name
----------- -------
-1459391524 Everest
-78983385 Acme

This Table lists various perils that might be covered; each may appear
in multiple policies.

[Detail] Table Datasheet view:

Detail_ID Type
----------- ----------
940690545 Earthquake
443127896 Fire
-450676459 Flooding
-536392113 Hail
-1066305205 Hair loss
-2146535970 Lightning
-2001082702 Loss of nest
2001901126 Meteor strike
-492063696 Tooth decay
-512829575 Volcano

In the next Table, I couldn't determine what [Policy Owner] might be.
You can determine the contact person by following links. For example,
in a child's policy, [Insured_Person_ID] identifies the child, and that
record's [Parent_Person_ID] link identifies the client who owns the
policy. Or, this field could be a direct link to the owner's record,
but it's usually good to avoid storing redundant information in a Table,
because maintaining it takes unnecessary extra work.

The lines were a bit too wide for email, so I split the Table's listing,
repeating the primary key, [Insurance_ID], to help keep track of the
records. (It appears only once in each record.)

[Insurance] Table Datasheet view:

Insurance_ID Policy Company_ID Policy Life
Number Owner Insured
------------ ------ ----------- ------- -------
-1041598040 882-22 -1459391524 B. Bird No
-543328349 179-33 -78983385 Snuffy No
-242941295 600-28 -78983385 Ernie No
-9603188 882-57 -1459391524 Maria No
852585834 772-63 -1459391524 Oscar Yes
998160080 885-42 -1459391524 Bert No
2058552749 816-80 -1459391524 No

Insurance_ID Insured_Person_ID
------------ -----------------
-1041598040 8940911
-543328349 -1071790618
-242941295 -1737207726
-9603188 -1987616873
852585834 -2135742055
998160080 -2135742055
2058552749 453834372

The next Table includes records for all contacts, spouses, and children,
with links to other information such as address, parent, or spouse.

[Person] Table Datasheet view:

Person_ID Title First Middle Surname Sex
Name Name
----------- ----- ------- ------ ------- ---
-2135742055 Ms Mary A Jones F
-1987616873 Ms June Jones F
-1737207726 Colleen Smith F
-1071790618 Dr George Jones M
8940911 Tracy Q Jones F
453834372 Billy Jones M
1942089013 Mr Sydney Smith M

Person_ID Spouse_ Parent_ IsCon Address_ID
Person_ID Person_ID tact?
----------- ----------- ----------- ----- ----------
-2135742055 -2135742055 0 Yes 155959414
-1987616873 -2135742055 0 No 173127785
-1737207726 0 1942089013 No 12245183
-1071790618 0 Yes 173127785
8940911 0 -2135742055 No 173127785
453834372 0 -2135742055 No 173127785
1942089013 0 0 Yes 12245183

The next Table contains only some links, in each record identifying some
insurance policy and some detail (in my example, the name of a covered
hazard) in that policy.

[Policy] Table Datasheet view:

Policy_ID Insurance_ID Detail_ID
----------- ------------ ------------
-1772889958 -9603188 443127896
-1286666971 -242941295 -512829575
-742185598 852585834 2001901126
-511421815 -1041598040 -536392113
-478125093 852585834 443127896
-218923041 -543328349 -450676459
1000970262 -1041598040 -2001082702
1179632046 2058552749 -1066305205
1191726976 998160080 -492063696
1407383937 998160080 940690545
1450169396 -242941295 -2146535970
1722141597 -9603188 -492063696

Now, you may well ask, what good is it? Is there a way to get a
meaningful list from these Tables full of ugly-looking numbers? Fear
not, it should be easy. For example, the next Query lists all the
hazards in all the policies covering a child of one of the contacts.

Notice that I renamed some of the references to [Person] to reflect just
what kind of person the reference intended to reflect. For example,
[Child].[First Name] actually looks up the [Person].[First Name] field,
but calling it [Child] helps us remember that we're looking at a child's
name, instead of a spouse's name.

[Q_Children's Policies] SQL:

SELECT Contact.[First Name], Contact.Surname,
Child.[First Name], Child.Sex, Address.Address,
Company.Name, Insurance.[Policy Number],
Detail.Type
FROM ((Person AS Child INNER JOIN Address
ON Child.Address_ID = Address.Address_ID)
INNER JOIN Person AS Contact
ON Child.Parent_Person_ID = Contact.Person_ID)
INNER JOIN ((Insurance INNER JOIN Company
ON Insurance.Company_ID = Company.Company_ID)
INNER JOIN (Detail INNER JOIN Policy
ON Detail.Detail_ID = Policy.Detail_ID)
ON (Insurance.Insurance_ID = Policy.Insurance_ID)
AND (Insurance.Insurance_ID = Policy.Insurance_ID)
AND (Insurance.Insurance_ID = Policy.Insurance_ID))
ON (Child.Person_ID = Insurance.Insured_Person_ID)
AND (Child.Person_ID = Insurance.Insured_Person_ID)
AND (Child.Person_ID = Insurance.Insured_Person_ID)
WHERE (((Contact.[IsContact?])=Yes))
ORDER BY Contact.Surname, Child.[First Name];

The results of running this Query look like this (but again I had to
split the output, which was too wide for the page):

[Q_Children's Policies] Query Datasheet View:

Contact. Surname Child. Sex
First Name First Name
---------- ------- ---------- ---
Mary Jones Billy M
Mary Jones Tracy F
Mary Jones Tracy F
Sydney Smith Colleen F
Sydney Smith Colleen F

Address Name Policy Type
Number
---------------- ------- ------ ----------
1234 E. Main St. Everest 816-80 Hair loss
1234 E. Main St. Everest 882-22 Loss of nest
1234 E. Main St. Everest 882-22 Hail
836 SE 127th St. Acme 600-28 Volcano
836 SE 127th St. Acme 600-28 Lightning

Please bear in mind that, though I entered these imitation data by using
Table Datasheet View, your users should use Forms to perform that
function. Using a Form will allow you to check for obvious mistakes
while a user is entering data, so you can help protect the contents of
the Tables from damage. Access provides a Wizard to help you generate a
Form, once your Query is working properly.

There is also a Report Wizard that will help you design a Report so that
you can see a list which suppresses repeating information, such as the
street addresses or the sponsor's names.

Hello, kingnothing, I'm back...

[quoted text clipped - 70 lines]

 

[kingnothing via AccessMonster.com]

Thanks David for your reply. I have just seen and posted my reply to Vincent,
i will try to do the things that you guys suggested.

Your contact table would have an AddressID field. You would then have another
table containing all the address information. This would also have an
AddressID field which will be the primary key. You then create a relationship
between the two tables (open the relationships window, add the two tables and
drag one AddressID field on to the other, click "enforce referential
integrity".

Each contact in the same household would have the same AddressID.

Dave

Please see inline..

[quoted text clipped - 41 lines]

 

[kingnothing via AccessMonster.com]

Yes Tom,

I will implement the suggested design from Vincent and put the screenshot of
the final design on the web for others to see.

Thanks,

kingnothing

Dear Kingnothing:

I have been looking at this site. Would you consider making it more useful?

Please make the width and height of the table rectangles large enough to be
able to see the width and height for all the columns and their names.

What I said about combining contact, spouse, and child into a single table
still stands, tentatively, as I have not really seen your design yet.
Please reveal it as I requested and post back here if you're interested.

Tom Ellison

Yeah i know about referential intergrity. so, you are saying that
relationships are absolutely imperative for the database design, right!!

[quoted text clipped - 32 lines]

 

[Vincent Johns]

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

During the course of the day, i will modify my message according to your
suggestion, and see how it goes.

Thanks

kingnothing

OK, here's a suggested (alternate) design for your Table structure.
Although I specified enforcing referential integrity for most links, I
did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

 

[kingnothing via AccessMonster.com]

This might seem a little dumb (Ever after you explainin in such detail) but
here goes...

This question has always plagued me for ages...

Do you do the relationships in the tbles (as in right click on tables section
and do the relationships) or do you leave the tables there and create a query
in design view, add all the tables, and create relationships there?? I'm
talking about the major relationships that are a part of your design...
Like for example, in your answer, do i just create tables and forget abt the
relationships and then create a select query and do all the relationships
there??? I dont get it...

PS: I will put up the db once it is done...so that other people can have easy
access to it and learn from it..

Help Vincent!!

Regards,

kingnothing

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

[quoted text clipped - 10 lines]

did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

 

[Tom Ellison]

Dear Vincent:

Create relationships in the relationship diagram. Start with a drag and
drop between the related columns in the two tables involved.

As I have seen your diagram, I would think this would be familiar if you
have done the work so far.

Tom Ellison

This might seem a little dumb (Ever after you explainin in such detail)
but
here goes...

This question has always plagued me for ages...

Do you do the relationships in the tbles (as in right click on tables
section
and do the relationships) or do you leave the tables there and create a
query
in design view, add all the tables, and create relationships there?? I'm
talking about the major relationships that are a part of your design...
Like for example, in your answer, do i just create tables and forget abt
the
relationships and then create a select query and do all the relationships
there??? I dont get it...

PS: I will put up the db once it is done...so that other people can have
easy
access to it and learn from it..

Help Vincent!!

Regards,

kingnothing

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

Thanks a lot Vincent, I really appreciate all the work that has gone
into
this reply. It is really helping me a lot.

[quoted text clipped - 10 lines]

did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

 

[Vincent Johns]

Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools --> Relationships menu, and you can add any or
all of the Tables in your database to that window.

This might seem a little dumb (Ever after you explainin in such detail) but
here goes...

This question has always plagued me for ages...

Do you do the relationships in the tbles (as in right click on tables section
and do the relationships) or do you leave the tables there and create a query
in design view, add all the tables, and create relationships there?? I'm
talking about the major relationships that are a part of your design...
Like for example, in your answer, do i just create tables and forget abt the
relationships and then create a select query and do all the relationships
there??? I dont get it...

PS: I will put up the db once it is done...so that other people can have easy
access to it and learn from it..

Help Vincent!!

Regards,

kingnothing

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

[quoted text clipped - 10 lines]

did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

 

[kingnothing via AccessMonster.com]

Thanks Vincent,

Thats what i meant, you can achieve Tools --> Relationships by right clicking
on an empty space in the sections where the tables live.

I am in the process of completely revamping the design, i will let you know
how i go

Regards,

kingnothing

Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools --> Relationships menu, and you can add any or
all of the Tables in your database to that window.

This might seem a little dumb (Ever after you explainin in such detail) but
here goes...

[quoted text clipped - 50 lines]

[...]

 

[Vincent Johns]

Aha... I hadn't tried that.

Anyway, good luck with your database. It will probably be 18 hours or
more before I return here to look at it, but maybe someone else will be
able to offer advice before then.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks Vincent,

Thats what i meant, you can achieve Tools --> Relationships by right clicking
on an empty space in the sections where the tables live.

I am in the process of completely revamping the design, i will let you know
how i go

Regards,

kingnothing

Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools --> Relationships menu, and you can add any or
all of the Tables in your database to that window.

This might seem a little dumb (Ever after you explainin in such detail) but
here goes...

[quoted text clipped - 50 lines]

[...]

 

[Tom Ellison]

Now, I would not have called a JOIN created in a query a relationship.
Relationships are part of table design and are enforced. JOINs can be made
on enforced relationships or not.

Relationships are always based on columns in two different tables that are
exactly identical. Queries can use non-equi JOINs that are in no way
relationships. So, while the two overlap in many cases, they are not
equivalent.

Tom Ellison

Thanks Vincent,

Thats what i meant, you can achieve Tools --> Relationships by right
clicking
on an empty space in the sections where the tables live.

I am in the process of completely revamping the design, i will let you
know
how i go

Regards,

kingnothing

Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools --> Relationships menu, and you can add any or
all of the Tables in your database to that window.

This might seem a little dumb (Ever after you explainin in such detail)
but
here goes...

[quoted text clipped - 50 lines]

[...]

 

[Vincent Johns]

Now, I would not have called a JOIN created in a query a relationship.
Relationships are part of table design and are enforced. JOINs can be made
on enforced relationships or not.

Relationships are always based on columns in two different tables that are
exactly identical. Queries can use non-equi JOINs that are in no way
relationships. So, while the two overlap in many cases, they are not
equivalent.

Tom Ellison

Thanks Vincent,

Thats what i meant, you can achieve Tools --> Relationships by right
clicking
on an empty space in the sections where the tables live.

I am in the process of completely revamping the design, i will let you
know
how i go

Regards,

kingnothing

Tom Ellison's advice is good, but actually you can create default
relationships in the Relationships window, and you can override them in
the Query Design View window. In some cases, you might even get by
without defining anything at all in the Relationships window -- but,
like Tom, I suggest that you set up as much as you can there first. If
you do so, then when you define Queries based on the related Tables, the
relationships will magically appear in Query Design View (saving you
some work). You can then alter or delete them, or add new ones, in
Query Design View (but you won't be able to specify Referential
Integrity there).

To answer your question, yes, you do need to define Tables, including at
least some of the fields in them, before you can define relationships
among those fields.

Incidentally, I'm not sure what you mean by "right click on tables
section". In my versions of Access, the Relationships window is
accessed via the Tools --> Relationships menu, and you can add any or
all of the Tables in your database to that window.


This might seem a little dumb (Ever after you explainin in such detail)
but
here goes...

[quoted text clipped - 50 lines]

[...]

 

[Vincent Johns]

This is a good point; I usually call both the enforced and unenforced
links relationships, but that means that I have to qualify my usage, so
perhaps it makes sense to refer to "Relationships" as distinct from
"JOINs". But then if you do make that distinction, how do you
(semantically) distinguish relationships (in the Relationships window)
that enforce referential integrity from those that don't, and from those
that don't cascade updates from those that do, for example?

Also, even though Jet SQL allows one to specify a JOIN on a ">="
relationship, doing so precludes using the Query Design View... so I
usually avoid that, instead specifying a separate condition to enforce
the ">=". That probably impacts performance (=makes Access do more
work, and be slower), but to me it's usually more important to be able
to easily see that the design is correct, and that's usually easier to
determine using Query Design View. So I try to minimize the kinds of
SQL that are not compatible with that. Therefore, in my Queries, almost
exclusively the links are of the "=" type, and they look and operate a
lot like unenforced relationships that might appear in the Relationships
window.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Now, I would not have called a JOIN created in a query a relationship.
Relationships are part of table design and are enforced. JOINs can be made
on enforced relationships or not.

Relationships are always based on columns in two different tables that are
exactly identical. Queries can use non-equi JOINs that are in no way
relationships. So, while the two overlap in many cases, they are not
equivalent.

Tom Ellison

[...]

 

[kingnothing via AccessMonster.com]

Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head.
I have designed just the basic stuff and have put a screen shot here -->
http://members.westnet.com.au/mukund/rel1.jpg

Person_1 == Inner Join for Spouse
Person_2 == Inner Join for Child

I hope everything is alright with this....

I could not enforce ref int as it was not allowing me to add data.

I realise this is just the begining....i will still need your help for the
later stages

Regards,

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

[quoted text clipped - 10 lines]

did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

 

[Vincent Johns]

I do have a couple of comments about your design.

Of course, you may name your fields whatever you wish, but I usually
give a primary key the same name as its Table, followed by "_ID" (and
avoid ending non-key fields with "ID"), and you are already doing that.
In addition, I give each foreign key a name that either matches that
of the matching primary key, or if there are two or more such in the
same Table, a name that ends with the primary key's name. So, instead
of key names like [Insurance].[Insurance_Person_ID], I would use
something more like [Insurance].[Contact_Person_ID],
[Insurance].[Spouse_Person_ID], or [Insurance].[Parent_Person_ID]. You
already know that these foreign keys are located in the [Insurance]
Table, so I think it's not necessary to repeat that information. The
"Person_ID" part of each name tells you that the key is supposed to
match the primary key in the [Person] Table.

More significant than the names you choose (though I think well-chosen
names can help considerably) is what each field means, how it relates to
the real world. What is the meaning of your
[Insurance].[Insurance_Child_ID] field? Since there is only one such
field in each [Insurance] record, I assume that it represents the
(unique) person who is a child who is associated with that policy; i.e.,
one policy in that Table cannot be associated with two children. Is
this what you want? Is it possible for there to be a record in
[Insurance] in which both the [Insurance].[Insurance_Child_ID] and
[Insurance].[Insurance_Spouse_ID] fields have non-null values? This
would mean that one policy covers both a spouse and a child, I suppose,
even though it cannot cover a spouse and two children. If you don't
want both fields to have non-null values in the same record, you can
write a Query which looks for that, but there might be an easier way to
enforce that restriction. (Since I don't know what your business rules
are, I'm just guessing here, but here is an area where clearly named
fields can call attention to places where they are used inconsistently.)

I have the same comment about the [Address] Table. Are you sure that
you want a one-child policy enforced for addresses, as you have done
here? There would, however, be no problem that I can see with having
both a spouse and a child attached to a given address.

My guess is that some of your links are backward -- for example, putting
a [Person].[Address_ID] into each [Person] record would allow you to
attach an address to everyone (even children), though you could leave
some empty to indicate, for example, that your last letter there was
returned as undeliverable. As you have it, you can list several
addresses for each child, or several insurance policies for a given
spouse, but I'm not convinced that that makes sense.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head.
I have designed just the basic stuff and have put a screen shot here -->
http://members.westnet.com.au/mukund/rel1.jpg

Person_1 == Inner Join for Spouse
Person_2 == Inner Join for Child

I hope everything is alright with this....

I could not enforce ref int as it was not allowing me to add data.

I realise this is just the begining....i will still need your help for the
later stages

Regards,

Great! As I may have mentioned, there's no one perfect way to design
your database, but trying to avoid duplicating stored information, using
meaningful names, etc., can save you a lot of work in the future. Good
luck.

Although others may differ with me on this, I think it's a good idea to
get your Tables and Queries working first, to be sure you've covered
everything you need (for now), and then, after they're working well, to
build your Forms (for data display and input by users) and Reports (for
data display and printouts) based on the Queries. Some people say that
you should never use Table Datasheets for entering data, but I think
that that's not harmful, and will save time, while you're trying to set
things up. Later, before you give your users access to the database,
you should define Forms that can provide a better user interface.

If you post a sanitized version (= without any real personal data that
might be of a sensitive nature) of your database on your Web site, so
that people could download it, that might save some work. Or at least,
maybe you could post example records from your Tables.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Thanks a lot Vincent, I really appreciate all the work that has gone into
this reply. It is really helping me a lot.

[quoted text clipped - 10 lines]

did not do so with the [Person] Table, because it interfered with adding
new records.

[...]

 

[kingnothing via AccessMonster.com]

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

I do have a couple of comments about your design.

Of course, you may name your fields whatever you wish, but I usually
give a primary key the same name as its Table, followed by "_ID" (and
avoid ending non-key fields with "ID"), and you are already doing that.
In addition, I give each foreign key a name that either matches that
of the matching primary key, or if there are two or more such in the
same Table, a name that ends with the primary key's name. So, instead
of key names like [Insurance].[Insurance_Person_ID], I would use
something more like [Insurance].[Contact_Person_ID],
[Insurance].[Spouse_Person_ID], or [Insurance].[Parent_Person_ID]. You
already know that these foreign keys are located in the [Insurance]
Table, so I think it's not necessary to repeat that information. The
"Person_ID" part of each name tells you that the key is supposed to
match the primary key in the [Person] Table.

More significant than the names you choose (though I think well-chosen
names can help considerably) is what each field means, how it relates to
the real world. What is the meaning of your
[Insurance].[Insurance_Child_ID] field? Since there is only one such
field in each [Insurance] record, I assume that it represents the
(unique) person who is a child who is associated with that policy; i.e.,
one policy in that Table cannot be associated with two children. Is
this what you want? Is it possible for there to be a record in
[Insurance] in which both the [Insurance].[Insurance_Child_ID] and
[Insurance].[Insurance_Spouse_ID] fields have non-null values? This
would mean that one policy covers both a spouse and a child, I suppose,
even though it cannot cover a spouse and two children. If you don't
want both fields to have non-null values in the same record, you can
write a Query which looks for that, but there might be an easier way to
enforce that restriction. (Since I don't know what your business rules
are, I'm just guessing here, but here is an area where clearly named
fields can call attention to places where they are used inconsistently.)

I have the same comment about the [Address] Table. Are you sure that
you want a one-child policy enforced for addresses, as you have done
here? There would, however, be no problem that I can see with having
both a spouse and a child attached to a given address.

My guess is that some of your links are backward -- for example, putting
a [Person].[Address_ID] into each [Person] record would allow you to
attach an address to everyone (even children), though you could leave
some empty to indicate, for example, that your last letter there was
returned as undeliverable. As you have it, you can list several
addresses for each child, or several insurance policies for a given
spouse, but I'm not convinced that that makes sense.

Hi Vincent, and all of you...thank you very much for helping me through. The
concepts have finally got into my head.

[quoted text clipped - 45 lines]

[...]