Need some input on MySQL schema

Question

I need some inputs on how I should structure my db.

Let's say I want to store members data in members table. There are two types of members, let's say person and organization. Person and organization have different set of information so that we have to store them into two different tables: let's say persons and organizations.

I would have the following tables:

members:

id
member_type_id
status
etc..

member_types:

id
type (person|organization|or any other type if needed)

persons:

id
name
etc

organization:

id
name
etc

My question is: Which one is the best of the followings?

1. Have member_id in both persons and organizations tables
2. Have an extra column in members table to store either person_id or organization_id
3. Have two extra columns in members table to store person_id or organization_id whichever is applicable

There is another requirement that there may be another table referring to both persons and organizations table. Let's say we have a table called visitors who can also be a person or an organization.

Thank you for your input.

Answer 1

Hi,

It is usually best to draw out your relationships so it is easier to understand how the 2 entities relate. This will let you easily say this is a one-to-one, many-to-one, one-to-many, or many-to-many, etc.

That being said once you figure that out, it is trivial to setup.

If you were using Java or .Net, you could use Hibernate/NHibernate to automatically build the tables for you by specifying the relationships.

Here is how I would design the tables from what you outline: (Keep in mind, this is following the Java naming schema)

MemberType
id     (int11, auto increment, primary key)
name   (varchar31, unique)

One note about MemberType, if you're using Java and you will not dynamically add/remove member types, you should use an enum here.


Member
id    (int11, auto increment, primary key)
memberType_id (int11, on update cascade, on delete cascade)


Organization
id    (int11, auto increment, primary key)
name  (varchar31, unique)

-- Depends on how you want this structured (I would design it as a many-to-many relationship
MemberOrganization
member_id       (int11, on update cascade, on delete cascade, this links to the member id)
organization_id (int11, on update cascade, on delete cascade, this links to the organization id)

To get a list of all the members of a particular organization ...

SELECT
 *
FROM
 Organization o
 INNER JOIN MemberOrganization mo ON mo.organization_id = o.id
 INNER JOIN Member m ON m.id = mo.member_id
WHERE
 o.name = 'StackOverFlow.com'

After looking at your question again, I think you're looking for a different answer.

Walter

Answer 2

You can also do this:

Member id name status

Person (extends Member) id (links to member id)

Organization (extends Member) id

Visitor id date member_id (again, this links to the member table, you will have to do an outer join to get the actual member data, either person or organization)

This uses a primary key join column and inheritance through separate table per subclass. In Hibernate, the queries are automatically written / handled for you, but they aren't that difficult to write.

Let me know if that helps.

Also, are you using PHP, Java, etc, and can you provide more background information into what you need or are using this for? There might be a better table design that is very different from this.

Like I said, there are tools out there that automate this stuff for you so you're not worrying about low-level details. Of course, you need to understand the relationship even before using the tools, otherwise, you won't be any better off. It is good to understand them, but the tools are optimized for 90% of the stuff you will need.

Walter