Jump to content

Contact tables


musicarteca
 Share

This topic is 5744 days old. Please don't post here. Open a new topic instead.

Recommended Posts

It depends on the context of how they are used and what's related to what, and what fields need to be remembered about each.

Employees and Supervisors are in one table because they really are all Employees (though an owner is not technically an Employee, it works best to include this special case in the same structure. After all, an owner still needs to do some of the same stuff in the database as everyone else.)

Teachers and Students are usually in separate tables because you need to remember different things about them, and they are in different contexts when you are talking about a Class. Same thing with Coaches and Players.

The situation with Clients and Suppliers depends on what these mean to you and what you need to do with the information. If "Client" and "Supplier" are just categories you assign to people you contact regularly, then you might keep them together in one table. But if there are a lot of relationships to these separate entities or they each have different things you need to remember about them, then it's easier to keep them separate.

At the extreme of normalization, one could use a Person table, and put fields in separate one-to-one tables for those things that are specific to different categories of people. But in my opinion, this doesn't really help the design or functionality of the solution.

Link to comment
Share on other sites

Thanks Ender, everything that you are saying makes a lot of sense. What concerns me is the fact that you can always have a contact with several roles, for example a student who is also a teacher, or a client who is also a supplier, so how do you deal in this kind of situation if they are in separate tables, duplicate the record?

Link to comment
Share on other sites

As I said, it depends on the context and requirements. If the main function of your solution is a contact database, and contacts have multiple roles, then it's probably easiest to use a Contact table with a one-to-many to a Role table. But in the case of a Student Management DB, the fields for a Teacher will be very different from the fields for a Student, and so they would be separate tables.

If it's a database that tracks classes and work history of grad students, then it may be useful to use one table to remember the Member, with separate tables for the Teaching Assignment role and Enrollment role. But for this case, I'd probably just relate the separate Teacher and Student records directly, indicating with an unstored calc if the Teacher happens to be a Student and if a Student happens to be a Teacher.

For any complex system, it's important to gain an understanding of the client's needs, and to construct an ERD that everyone understands. From that it's usually clear whether they're talking about separate Clients and Suppliers that happen to be contacted periodically, or Contacts that happen to be Clients or Suppliers. :)

Link to comment
Share on other sites

This topic is 5744 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.