guidence/advice for tables

[bach]

Hi,

I am trying to create a database but I am a little stuck, could do with your input.

Here is the table structure so far:

member_tbl
id AutoNo Pk
memberid speacial number given to each member
title fk - linked to title table ?
fname
mname
lname
addid fk - linked to a address table ?
datejoined
phone
mobile
email
dob
role fk - linked to role table

address_tbl
id pk
house no/name
streetadd1
streetadd2
area
postcode
town/city
postcode

address_r_tbl
id pk
addid pk

role_tbl
id pk
name

role_r_tble
rid pk
mid pk
sdate
edate

family_tbl
fid pk
phone
date joined
email
default contact - not sure if this should be here or fmembers_tbl.

fmembers_tbl
mid pk fk - using info from members table ?
default contact - not sure if this should be here or family_tbl.

My issues are this

1. title - Should this be a separate table.
2. address - should this be a separate table.
3. role - not sure how to set the id field for this, I have read that autonumber for a small number of items in the table is not worth it.

Would be greatful for your input.

 

[bach]

Thanks.

Pat Hartman

Thanks for your reply.

I am creating a membership database.

1. I keep all single field lookups in a single table of tables. I have developed a form and report to manage them that I place in all applications. If you don't want to go that route, you can do as you have done or you can create a lookup at the table level with the text values - Mr., Mrs., etc.

table of tables, sorry but I don't understand what you mean in the above paragraph.

2. With your present design, you can share addresses. If you don't have the need to do that, then don't create a separate table. If you do need to share addresses, you'll need to be very careful when updating them to ensure that the change applies to ALL users of the address, and not just to one person. The other way to share addresses allows for a person/company to have multiple addresses. In that case, the addressID would not be placed in the member table. It would be placed in a third table called a relation table. The relation table would have MemberID, AddressID, and RoleID. The Role would define the use of the address - mailing, billing, summer mailing, etc.

I am thinking of putting in a family table so that members can be linked within the database. This family table/s would need to handle separate address if neccessary for people in the same family. Would this effect how these tables would be designed ?? In regard to the statement addressid would not be placed in the member table i accept this, would you explain why is this a better practis.

3. I don't know what role is in this context. Is a member restricted to one role? Usually people can assume multiple roles and in that case, you would need a relation table similar to what I described in answer 2. It would contain PersonID, RoleID and perhaps some other information related to the person in that role such as start and end dates for terms of office.

The role table show what each member does in the church (deacon, reverent, member, etc.) . A member can have more than one role.

Thanks again for your reply. I am thankfull for any info/help/tip you can offer.

Bach.

P.S. - I've added in the family tables.

 

[bach]

Anyone able to advice.