Contact tables

[musicarteca]

Just a curiosity: Do you usually use the same table for several contact categories, like for example: clients and suppliers, or employees and supervisors, what about teachers and students, or coaches and players?

[Ender]

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.

[musicarteca]

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?

[Ender]

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. :)