A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Design Problem



 
 
Thread Tools Display Modes
  #11  
Old August 15th, 2005, 04:16 PM
Bryan Hughes
external usenet poster
 
Posts: n/a
Default

Tim,

I am sorry I have been unclear.

Here are the main entities.

Family (Case File)
Family Members (Clients)
Family Contacts (Non-Clients)
Addresses
Phones

... there should be one person attached to a file with primacy, and
that there should be one address linked to that person with primary, then
we are back on track.


Yes this is what I mean.

I should have clarified better from the begnning.

The main purpose of the db is to track Families (Case File). Within each
family they want to track Family Members (Clients-which they are calling
Personal Journals will leave that out for now).
Basically they (the people I am building the db for) want the Family as the
first enity, and then within the Family the want to track the Family Member
(Client) as a unique enity (A file within a file). The contacts are
strictly contected to the family entity.

I hope this helps and does not muddle it more.

-TFTH
Bryan


  #12  
Old August 15th, 2005, 06:55 PM
Bryan Hughes
external usenet poster
 
Posts: n/a
Default

Tim,

This is what I have now

Family
MFIDS PK (Auto Number)
FID Long
FJID TEXT (MAIN ID for table)

People
IDS PK (Auto Number)
PJID PK
FN
LN
MI
Type (family member, or type of non-family member)
Primary (Use as primary contact)
Main (Use as main family name)

Personal (Client)
IDS PK (Auto Number)
PJID PK
DOB
etc.,

Addresess
IDS PK (Auto Number)
FJADID PK
FJID FK
Address1
Address2
City
etc.,

Resides
IDS PK (AutoNumber)
PJID FK
FJADID FK


Phone
IDS PK (Auto Number)
FJPNID PK
FJID FK
Phone
Type
Primary

Call
IDS PK (Auto Number)
PJID FK
PJPNID FK
Primary

Does this look right? That way there can be one address for many people. I
can check for a address for a Person (Client) and if there is non found then
a query can find the primary address for the Family File.

-TFTH
Bryan





  #13  
Old August 16th, 2005, 05:42 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Bryan Hughes" wrote in
:

The main purpose of the db is to track Families (Case File). Within
each family they want to track Family Members (Clients-which they are
calling Personal Journals will leave that out for now).


Can I make a plea -- that you think really hard about your naming
strategies? In six months' time, you (or, even worse, somebody else) will
be staring at acronyms like PJID PK and wondering what on earth it means.
Typing a little bit more now will save a huge amount of effort later. If
that means PersonalJournalID then call it that. And don't call it
Families sometimes and Files another time. In my opinion, it's better to
stick to simple words like People and Addresses and LivesAt for the same
reason, and because also they are free from implications about
functionality. I agree that that is a matter of personal style and taste,
though.

DB design is all about semantics: the best solution often comes down to
questions like "What exactly is an address (for mailing? visiting?
identifing? liability for services?)" Don't worry about going back to
your people and asking picky questions like "what _exactly_ are you going
to use this DateOfBirth for?" It makes a difference.

This is what I have now

Family
MFIDS PK (Auto Number)
FID Long
FJID TEXT (MAIN ID for table)


Phrases like "Main ID for table" don't help. If it's a unique and stable
identifier, then it's the primary key; if it's not then it's just a field
like all the other ones. You don't give any clue about the use or meaning
of MFIDS or FID so I can't comment.

People
IDS PK (Auto Number)
PJID PK
FN
LN
MI
Type (family member, or type of non-family member)
Primary (Use as primary contact)
Main (Use as main family name)


If only clients can be Primary Contacts, then the Primary field belongs
in the Clients table. I don't see any foreign key pointing at the Files
(sorry, Family) table: how do you know which file this Person belongs to?
You have two fields labelled PK -- you can't have two PKs in a table, and
there is no point in using an autonumber as part of a compound PK,
because an autonumber is already stable and unique. Is a Main Family Name
an attribute of the person, or of the Family (and thus of all the People
in that Family)?

Personal (Client)
IDS PK (Auto Number)
PJID PK
DOB
etc.,


Hmmm: my original suggestion made this a subtype of the People table, so
the PK would be a Long Integer that matches the autonumber PK of the
People table. You can't point an autonumber at another autonumber because
you can't choose what to put in it.

Addresess
IDS PK (Auto Number)
FJADID PK
FJID FK
Address1
Address2
City
etc.,


I still don't understand what all the fields labelled PK are for. If this
is a straightforward Addresses table, then you just need an autonumber PK
and the text fields.

Phone
IDS PK (Auto Number)
FJPNID PK
FJID FK
Phone
Type
Primary


Ditto

Resides
IDS PK (AutoNumber)
PJID FK
FJADID FK


All you really need in this table is two fields: a Long Integer pointing
at the People table and a Long Integer pointing at the Addresses table.
You need some kind of AddressType indication somewhere. If a particular
address is always (R)esidential or (W)ork etc, then you can keep it in
the Addresses table. On the other hand, if Jones resides at Tolpuddle
Flats, while Smith works there, then it's an attribute of the Resides
table. If you see what I mean.

Call
IDS PK (Auto Number)
PJID FK
PJPNID FK
Primary


Ditto: I assume this is the table that tells you which PhoneNumber
belongs to which Person.

There is quite a lot there. I get the impression that you need to
understand more about keys and what Primary Keys do. It's not just a
question of stuffing an autonumber field in all the tables: you need to
have some conception of what you need to achieve.

All the best


Tim F


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Design View problem Access 2003 Alex Using Forms 1 June 22nd, 2005 09:55 PM
Table Design Problem? B Karthick General Discussion 1 April 16th, 2005 09:36 AM
Problem found when exporting e-mails to OL2003 Sheldon Tam General Discussion 0 April 5th, 2005 11:41 PM
Problem Bullet Point - Layout or Design? John Powerpoint 1 June 29th, 2004 04:30 AM
Productkey problem when installing office 2003 on network Stefan Schreurs Setup, Installing & Configuration 1 June 1st, 2004 11:16 PM


All times are GMT +1. The time now is 10:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.