Hi Art,
adding on to Graham's excellent advice...
the video tutorials on my YouTube channel take this approach ... well the Role tables aren't there since the tutorials are not that far along yet. I might normalize addresses and other contact information more than you want for your needs -- but the basic structure is the same idea: One table for People. I include companies in the People table. Now my databases call this main table Contacts.
The Contacts table uses an autonumber ContactID
I also throw this field in there:
ContactID_, long integer
when I have a field in a table that is the primary key plus an underscore, my personal logic is that this means it is related to another record in the same table. for contacts, you can use this field to identify everyone in a household or everyone in a company. For instances where more than one relationship is needed, you obviously need another table. for your needs though, this method will work
btw, your computer clock is a day off ...
Warm Regards,
Crystal
*
(: have an awesome day :)
*
________________________________
From: Graham Mandeno
Subject: RE: [MS_AccessPros] Single table for client and household
Hi Art
I think this is a common scenario, where people in an organisation can have
multiple roles. Each person has a name, contact details, and other common
data, but there are specific data for each role as well, and the same person
could assume more than one role.
For example, in a learning institution where you have students, teachers,
administrators and technical staff, a person could play all four of these
roles, while most would be associated with only one.
The approach I use is to have one "Persons" table containing all the
"common" data, then one table for each role, with a one-to-one relationship
to Persons. For example, the Teachers table would have fields relating to
qualifications and areas of expertise and courses taught, while the Students
table would have links to courses studies and grades.
If someone who is both a teacher and a student has a change of address, then
this needs to be updated in only one place.
Just my 2c worth :-)
Best wishes,
Graham
> From: Art
>
> Does any one have an idea on how can develop a single table that would
contain a client data as well as household members data? It would need to
demographic data for the client (name, address, dob, ssn, etc) and as well
as for household members.
>
> I thought o do it in two seperate tables but that would lead to redundant
data in both the client table and the household member table for the client
record.
>
> If there was sample somewhere for me to look at it would be great.
>
> Thanks
>
> Art Lorenzini,
> Sioux Falls, Sd
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar