Tables with common fields

[chobo321321]

I'm not sure what this kinda of table setup is called, or if even has a name. I Have four tables:

tblEmployees
tblSalesPerson
tblRepairman
tblCustomers

Each of these tables has common fields such as:

Name (first/Last)
Address
Phone#
etc...

Should I make a table separate table that contains the common fields among all four tables (tblPerson)? If I would make a tblperson, and give it a primary key like "personID(autonumber)" would that become the foreign key in the other tables? I'm kinda of confused on how to make the relationship between the common table with the other ones. Sorry if this question doesn't make much sense, I'm not too handy with access.

Thanks in advance.

 

[chobo321321]

I tried having a common table, but I am having trouble with data entry. I want to fill in the PersonalClient, or BusinessClient table first, but I can't since I need to have a clientID number. If I fill in the client table first it jsut doesn't make sense to me... It does give me the option to link to one of the other tables, sort of like a subform for filling it out, but that doesn't help when I need to make a record of using the other table. Here is an example of what I am taking about.

To me the relationship makes sense, but if it seems kinda of scrwey or wrong please let me know.

 

[yhgtbfk]

I would duplicate the table headings.

What you have to remember is that each sales person or customer only has ONE address, or ONE phone number, so putting it in a seperate table makes no sense, and is harder to maintain.

Correct relationships and data normalisation is vital for databases, and I would advise understanding that first.

tblEmployees
Name (first/Last) Address Phone# etc...
tblSalesPerson
Name (first/Last) Address Phone# etc...
tblRepairman
Name (first/Last) Address Phone# etc...
tblCustomers
Name (first/Last) Address Phone# etc...

 

[Pat Hartman]

I am not sure what the first and second posts have to do with each other. Regarding the first post, you should not have four separate tables for people. You should have one. If you are absolutely, positively, completely certain that there will never be any overlap with the same person being in more than one table, you can add a Role code to the person table to indicate that the person is an Employee, a SalesPerson, a Repairman, or a Customer. If you can forsee the possibility of a person serving multiple roles, make a many-to-many relationship between person and role.

 

[neileg]

What you have to remember is that each sales person or customer only has ONE address, or ONE phone number, so putting it in a seperate table makes no sense, and is harder to maintain.

Is that true? I know plenty of people that have more than one phone number and /or address. It may be that in this application it's not relevant to store more than one address, but you can't assume that without further qualification.

 

[chobo321321]

Thanks for the help guys. I just brought that up cause I recalled my access teacher saying that if there are tables with common fields you could lump them together in one generic table, and link it to other tables that that would contain the fields that are not common, but I have tried that and data entry is a nightmare. I'm not sure if anyone else has heard of doing it like that, or had experience with that.

 

[yhgtbfk]

It may be that in this application it's not relevant to store more than one address, but you can't assume that without further qualification.

My assumption was based on clientID being a primary key in the ClientTable.

If it were true that one client could have many phone numbers within the database, then clientID would be a foreign key, and there will be another unique field (probably an autonumber).

But very good point though.

 

[neileg]

Thanks for the help guys. I just brought that up cause I recalled my access teacher saying that if there are tables with common fields you could lump them together in one generic table, and link it to other tables that that would contain the fields that are not common, but I have tried that and data entry is a nightmare. I'm not sure if anyone else has heard of doing it like that, or had experience with that.

Not having any formal db training, I don't get overly worried about normalisation. You need to be practical about this. If the number of fields is small, I would accept a compromise that allowed there to be redundant fields in the generic table. If there's a lot of data and the differences are significant you may need separate sub tables for each type of person. This may make matters more complex later, especially if one person record can be more than person 'type'.