Help with designing some tables

[Zaeed]

Hello,

I currently have a database thats working ok, however its not as efficient as it could be, and certain changes (which will be happening more often in the future) cause a lot of hassles, so i'm looking to redesign this certain area.

I must admit my knowledge of table design and structure is rather limited, so please bear with me.

Ok, basic overview of my database.
Its a system that tracks changes to my company. A user raises a change, and outlines details such as title, description, location, tasks, and impact areas. Depending on what information is entered, certain people are selected to be notified of the change. The location is the area that I wish to rebuild.

In my mind, the layout I wish to build would be to have a location, say Home as an example. Home should have a Primary contact person, and then a list of Secondary contact people. So as an example: Father might be the Primary Contact for Home, and Mother, Son, Son2, and Daughter would be the Secondary Contacts.

This would then allow for an email to be sent to either the Primary Contact person for Home, or all the Secondary Contacts, or both.

I would also need to be able to easily change Father to Uncle Bob if the need arises, and add/remove people to the Secondary Contacts

Does that make sense?

Thanks for your help.

 

[John Big Booty]

How about something like?

TBL_Contact
ContactID (Auto Number)
EmployeeID (FK)
TitleID (FK)
ContGivName (Contact Given Name)
ContFamName (Contact Family Name)
ContactPrec (1=Primary, 2=Secondary)
RelationID (FK)
Active (Yes/No)

TBL_ContactLocation
ContactLocID (AutoNumber)
ContactID (FK)
LocationID (FK)
PhoneNum (Land Line Number)
MblNum (Mobile Number)
ContEmail (Contact Email Address)

TBL_Titles
TitleID (Auto Number)
Title (Mr., Ms., Mrs. etc.)

TBL_Relation
RelationID (Auto Number)
Relationship (Mother, Father, Uncle, etc.)

TBL_Location
LocationID (Auto Number)
Location (Home, Work, Etc.)

 

[Zaeed]

The family case was just meant as a simple example.. the real situation is a factory with different departments, and i need to be able to assign a manager, and then a group of contacts that are only there for notification..

In my db, if a change is raised and requires approval before it proceeds, then the manager for the set department is required to approve it. once it is approved, that manager and all the secondary contacts are contacted..

So I need to be able to easily identify who is the assigned manager for a department for emailing purposes. And also easily be able to extract all the secondary contacts.

 

[John Big Booty]

The family case was just meant as a simple example.. the real situation is a factory with different departments, and i need to be able to assign a manager, and then a group of contacts that are only there for notification..

.............

In which case you should still be able to adapt my example to your requirements. If not, why provide a hypothetical that doesn't relate to what you are really looking for

 

[Zaeed]

Sorry, I was just trying to simplify my case.. I should be able to modify what you gave me yes.

How would I go about refrencing all the secondary contacts? as in if user1, user2, user3 were the secondary contacts, how would i extract them to build a list?

 

[John Big Booty]

........

How would I go about refrencing all the secondary contacts? as in if user1, user2, user3 were the secondary contacts, how would i extract them to build a list?

Firstly you would query your contacts and only select those checked as current. Then you could give your contacts an incrementing number to represent their order of precedence, and sort accordingly. Using this method, you would have to reassign priority to contacts and new contacts are added or and old ones become non current.

Alternatively, if secondary contact precedence is not important, simply give your primary contact a value of one(1) and all other contacts a value of two (2)