Database Design (Chapter 6)

[SteveM]

I have a question regarding database design.

I am creating an online directory.  I want users to be able to register and log in as a "Profile Owner", and then create profiles for different busninesses or organizations. This would be a 1 (owner) to many (profile) relationship.

Now I run into a problem of how best to design the database. I want to create different tables for different profile types (i.e. types of directory listings). For example, if a user creates a profile for a church, the Profile Type would be "church" and have its own set of attributes stored in the "church" table. If a user creates a profile for a library, the Profile Type would be "library" and have a different set of attributes stored in the library table.

Question: How do I link rows in different Profile Type tables to rows in the single Profile table?

TABLE: profile_owner ( primary key: profile_owner_id)
TABLE: profile (profile_id, profile_type, primary key: profile_id, foreign key: profile_owner_id)
TABLE: church (church_id, foreign key: profile_id)
TABLE: library (library_id, foreign key: profile_id)
etc...

So to access attributes from the Profile_Type tables, I would reference table.column: church.profile_id or library.profile-id, etc.

The problem is that the parent id (primary key) of the profile table has a child (foreign key) accross any one of multiple tables. A profile_type row can be linked back to the profile through the profile_id. But given a profile_id, I also need the profile_type to know which table to look in.

Is there a better way to do this? Hopefully this isn't too confusing. Thanks for any advice!

[Larry]

So sorry for the delayed reply! First, do you still need help with this? Second, it is possible--but not great--to dynamically do JOINs across tables in a situation like this. Alternatively, you could not do JOINs and do two separately queries when the time comes. A third option is to create a VIEW table that does the joins for you and then you run SELECT queries on that. 

 

Thinking about it more, how different are the profiles? Couldn't you store all the profiles in one table and have a "type" column in it?