Database planning- relationships

[margita]

I am planning a database of Organisations and Staff.

The idea is to show Organisations with people employed by these organisations. Organisations would be categorised into relevant categories (such as Affiliates, Service Providers or Training). Organisations would always have the same address with the main phone number, fax number and main email which would be common to all staff. However, staff members could have several phone numbers or emails (work, personal, mobile etc). In addtition to this staff members could have different roles such as Trustee, Director or Policy Officer.

My plan is to have three tables: Category, Organisations and People. Category is one-to-many to Organisations. Organisations is one-to-many to People.

tbl Category

Category ID

Category

tbl Organisations

Organisation ID

Category ID (FK)

Organisation

Address Details

tbl People

People ID

Organisation ID (FK)

First Name

Last Name

Then there would be tables Phone, Email and Roles:

tbl Phone

Phone ID

Phone

tbl Email

Email ID

Email

tbl Roles

Role ID

Role

Now I would probably have to create join tables between tbl People and tbl Phone, between tbl People and tbl Email and between tbl People and tbl Roles. The join tabels would consist of People ID and Phone ID and a field for phone mumbers, People ID and Email ID and a field for email addresses, and People ID and Role ID with a field for roles ( however, that may be difficult because it will not always be known if all the roles are entered in tbl Roles - there may always be a new role at some point).

Would I be on the right track?

Now my problem is that I would need to create a layout for Data Entry. I am not sure if it would be possible to have everything on one layout.

If it was a simple database with Organisations and People, then the layout would be straightforward:

a tabbed layout showing Organisations on the first tab with their addresses and then a portal of people on the second tab with their names.

But I would not be able to have their several phone numbers or emails or roles on the same layout.

Would you be able to advise me if the database schema is correct and if so how can I simplify the data entry as it may be annoying to go from one layout to another?

Many thanks.

[Ender]

Here's an ER diagram showing how this should probably be structured.

Note that the Email table is only needed if each Person might have multiple email addresses that need to be remembered, otherwise, just use an Email field in Person. The only join table needed is Assignment, to allow a Person to have multiple Positons (Roles) over time.

Although there are a variety of ways to handle the flow of data-entry in a multi-table solution, it's best to start with the parent tables and work your way down. In this case, I'd start by having the user find for the Organization they wish to add, if it already exists, they can check if the Person already exists. If the Organization does not exist, they can add a new one, then enter a new Person. When it's time to add an Assignment, they would use a value list of the Positions that are defined for that Organization, if the desired Position does not exist, they could step out of the "Add Person" process to create the Position. Phone numbers and Emails could be easily entered through a portal on the Person data-entry layout.

Edited June 3, 2006 by Guest

[margita]

I am very glad that I had a reply - thank you very much.

I have been thinking about your suggested schema.

The reason why I thought about a join table for the phone numbers is that a person may have a direct number at work, mobile phone number, home phone etc.

I usually have join tables for phone numbers as it makes it easier to enter several phone numbers for one person. The same would apply if a person had several email addresses such as work email, personal email etc. Do you think it is a bad idea?

I wonder if we could discuss the Assignment table. In my particular eaxmple we would not be talking about assignments - they are not assignments and there are no dates for assignments. This database is in fact a database of contacts. But because each contact is in fact an organisation with many people who need to be in the database as well, I thought that the best way would be to have the Organisation table as a parent table rather than have just People table with many duplicate organisations.

But I would need a table Role (or Assignments) which as you say would be a join table - but there are no dates in that table. The roles would be necessary for, let's say, mailshots which would go to people with certain roles etc. From your schema I can see that Organisation table is related to the Assignment table. I don't really understand that? Does it mean that the Assignment table is related to the Organisation table by the Organisation ID as a foreign key? I think this would not be workable in my case. Shouldn't the Assignment (it should really be Role) table be related to the People table by the People ID as the foreign key?

From what you say it sounds as if it will not be possible to keep everything on one layout for data entry or for searching. I would have to go for your approach and hope it will work out.

I hope you will come back to me regarding my comments above.

Edited June 3, 2006 by Guest

[Ender]

No problem. Let's look at each concern in turn:

The reason why I thought about a join table for the phone numbers is that a person may have a direct number at work, mobile phone number, home phone etc.

I usually have join tables for phone numbers as it makes it easier to enter several phone numbers for one person. The same would apply if a person had several email addresses such as work email, personal email etc. Do you think it is a bad idea?

In the case of phone numbers and email addresses, it is desirable to remember many Phone numbers for each Person, and many Email addresses for each Person, but it is rarely useful to remember many People for each Phone number or many People for each Email address (if two People happen to share the same phone or email, who cares?) Because of this, the one-to-many relationships as shown are all that's needed.

I wonder if we could discuss the Assignment table. In my particular eaxmple we would not be talking about assignments - they are not assignments and there are no dates for assignments. This database is in fact a database of contacts. But because each contact is in fact an organisation with many people who need to be in the database as well, I thought that the best way would be to have the Organisation table as a parent table rather than have just People table with many duplicate organisations.

But I would need a table Role (or Assignments) which as you say would be a join table - but there are no dates in that table. The roles would be necessary for, let's say, mailshots which would go to people with certain roles etc.

Think of that Assignment table as an assignment of a Person to a Position within the organization, not as a homework assignment. I suppose the dates of such an assignment are only important if you're working with People in your own organization. Since your Organizations are external, you probably won't know or care about the dates, so you can skip those.

From your schema I can see that Organisation table is related to the Assignment table. I don't really understand that? Does it mean that the Assignment table is related to the Organisation table by the Organisation ID as a foreign key? I think this would not be workable in my case.

The Organization table is linked to the Position table by Organization ID. The purpose of this relationship is to signify that the Positions are specific to the Organization. If this isn't important, then the Position table itself can be skipped.

Shouldn't the Assignment (it should really be Role) table be related to the People table by the People ID as the foreign key?

Yes, in reading the ER Diagram, you can assume that the link between entities implies a foreign key corresponding to the primary key(s) in the related table(s). In the case of the Assignment table, there would necessarily be a foreign key for Person ID, and if you're using the Position table, the Assignment table would also have a Position ID.

From what you say it sounds as if it will not be possible to keep everything on one layout for data entry or for searching. I would have to go for your approach and hope it will work out.

In some cases, finds or data entry of related tables can be done from a single layout, but this is not typical. Remember, each layout is based on a single table occurence, so it does not work to use that single layout to do all things. As an example, if you wish to allow a Find, you have to decide what table is being searched, and therefore which records will be the results. If a Find for a particular Position is made through a layout based on the Organization, the result will be Organizations that have that Position. If a search for a particular Position is made through a layout based on the Person table, the result will be Person records that are assigned that Position. The result set depends on the context of the layout.

The context of the data-entry layouts should be clearer. In general, data entry should be done in a layout based on the same table as the fields. An exception is using a portal to create child records from a layout based on the parent. But creating a parent record from a layout based on the child is trickier.

[Fenton]

The Organization and People tables' data entry depends partly on whether there's always an organization for each person; and also on how many records you expect to have (ie., hundreds, thousands, hundreds of thousands). As Ender says, it's hard to create parent records from child, a little awkward even to pick a parent, if there's thousands of parents.

Your statement "each contact is in fact an organisation with many people" implies that EVERY person belongs to an organization. If that is true, then it's fairly simple. Only allow creation of a new person from the Org. table; probably using a button, so they end up on the People table form view. Tie them to the Org. right then.

Otherwise, if a person may or may not belong to an org., then you'll have to give them a way to pick which. If there's many records, this should be from a filtered list (a plain drop-down list can be slow with thousands of entries). There are various ways to do that.

[ I favor a method I learned from John Mark Osborne's file "AUTOCOMP.FP7", which was in a collection of his files from the FileMaker Developer conference of 2005. Very clever. It is for first-last names, but could be adapted for organizations; by creating a stored calculation field for each possible word. Beats heck out of "exploding" the words; though it may be a little slower.]

http://www.filemakerpros.com/DevCon05.sit

As far as other things to do with Orgs and People, such as Notes, etc.. You can auto-enter the OrgID (if any) into Notes. So that even if created from People, the note can show in Org. You can also filter those created in Org from those created in People; show in different portals.

[margita]

Thank you so much for your reply.

It is interesting what you say about the phone numbers. I only thought that that the join table would make it easier to just choose a phone type from the pop-up field in the portal in the data entry layout - rather than create a record for the phone type every time with the one-to-many relationship. Or am I missing something? I have never created several phone numbers in this way, so I don't know how this works in practice.

The Assignment (or Role) table would not be specific for the organisation. So can I assume that it would be alright to have a table called Positions (or Roles) and enter records for Roles.

And then I would create a join table for People and Positions (or Roles) as People can have several roles?

I am also replying to Fenton's comment in a separate message.

[margita]

Thanks very much again.

Just to clarify People and Organisations. There would be many organisations - not just one. Each organisation would have a list of people belonging to that organisation. The scenario is that it is a federation which would have a database of its contacts consisting of member organisations, service providers etc.

I think your tip will be very useful once I've had a chance to study it properly. I'll probably will have to create a false tab in order to go from Organisations data entry to a single People contact belonging to that organisation to fill in more data for that person.

I am greatly interested in the ultimate find which I hope to implement successfully in different databases.

I hope you won't mind if I have another question for you which is part of the man-to-many relationships we discussed. As this does not belong here. I will have to go back there and I hope to hear from you.

[waharris]

Moved - to its own thread

Edited June 4, 2006 by Guest

[Ender]

We don't mind helping, but since margita's questions may not all be answered, and your questions are unrelated, it would be better if you put this in your own thread.