Question about table design

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
Could someone help with a contacts database that I am designing. I'm getting
stuck on how to organize the tables!
Basically I need to know what to do when you have a contact that could have
several different roles. i.e He could be MD, Chief Exec, Chairman etc - so a
contact could have any number of different roles. Also contacts are all
members of different working groups but can be a different type of member
dependent on the group. i.e Person a might be a core member of the cocacola
group but the lead member of the pepsi max group. Hope all that makes sense!
Thanks in advance for any help.
 
Hogan said:
Hi
Could someone help with a contacts database that I am designing. I'm
getting stuck on how to organize the tables!
Basically I need to know what to do when you have a contact that
could have several different roles. i.e He could be MD, Chief Exec,
Chairman etc - so a contact could have any number of different roles.
Click to expand...

Individual table
Roles table
WorkingGroup table (containing check boxes for core/leader etc.)
 
You should try to analyze your info and check which information would
be double entered in a table. That info should be split into another
table and linked to the original. It's called a
'one-too-many'-relationship. These are the most commonly used type in
databases. If, as it seems in your case, there are multiple
possibilities on both sides (a contact could be many roles, and one
role could be filled in by many contacts) you would get a
"many-to-many"-relation. That's a bit more awkward.

For instance, you say a contact can be an MD, Chief Exec, Chairman etc.
If a contact can only be ONE of these at a certain time, then you can
enter both info into one table (column1 + column2):

John | MD
Jack | Chairman
Jim | MD
Jacob | Chief Exec

This is a 'one-to-one'-relationship and there is no need to split those
into two tables.

However, say a contact could be AND MD AND Chief Exec AND Chairman. If
you would put this into one table, you would get something like:

John | MD
John | Chief exec
Jack | Chairman
Jim | MD
Jim | Chief Exec
Jim | Chairman
Jacob | Chairman
Jacob | Chief exec

You would have to make a row per role, which obviously is a waste of
space. You would have to enter f.i. "jim" three times, not to mention
the pain it would be when Jim retires and is replaced by Jill: You
would have to edit the table on 3 places.

So what you do then, is split the info into 2 tables, and link them
through a UID (Unique Identifier). In the above example, you would make
2 tables; one with the contacts and a reference, and one with the roles
and a UID (which is linked to the other tables reference). One of those
tables would need to have the possibility to have references to more
then one UID in the other table, so you would need to make more cols in
the table, f.i.:

name | role1 | role2 | role3 |
John | 1 | 2 |
Jack | 3 |
Jim | 1 | 2 | 3 |
Jacob | 2 | 3 |

and:

UID | role
1 | MD
2 | Chief Exec
3 | Chairman

Now I admit that this LOOKS like a lot of extra work, without much
gain, but imagine this being 200 employees and 25 roles and you having
to change 9 employees' names, or change 'MD' into "Medical Doctor"...

Same with the groups: A table should be made of the different groups,
and one of the different roles, so reference to both of them can be set
in the 'base'-table with the names of the contacts. Again, you should
try and think of it more mathematically then based on reality: Say both
CocaCola and PepsiMax have a role called 'leader'. The only reason to
create two 'leader'-entries in two tables would be that the
PepsiMax-leader would have different characteristics (which also need
entered in the database) then CocaCola-leader.

If this is very improbable, make one table with one 'leader' and link
both the pepsimax and cocacola-leaders to them. It's just a name then.

The above looks tedious, and it is, but I advice you to think it
through very thoroughly, because these few days of thinking and
analyzing can save you weeks and weeks of time later on when your
database is made and you suddenly realize it has to be differently. ;-)
 
Hi - thanks for that. The check box kind of makes sense but I'm not sure how
to do it - and how is that information stored in the various tables?

i.e. When you are in form view how would the user select multiple working
group memberships and also tick what type of member they are for each group.
thanks
 
Hogan said:
Hi - thanks for that. The check box kind of makes sense but I'm not
sure how to do it - and how is that information stored in the various
tables?

i.e. When you are in form view how would the user select multiple
working group memberships and also tick what type of member they are
for each group. thanks
Click to expand...

When you select a check box it appears "checked" for that one record. I
was suggesting different check boxes for each type of member. Normally I
would guess they would only be one kind of member per group but you could
check more than one if needed. With the relation indicated you would have a
new lest of types for each group the person was in.
 
Sounds like you need to setup multiple tables and establish a relationship, a
one to many relationship. This way one person can have multiple roles with
different titles. Example, tables with contact names and group names. The
role and title will be in the group name table with a link to the names table.

Tim Lange
West Lafayette, IN USA
(Go Boilers!)
 
Back
Top