Jump to content
Larry Ullman's Book Forums

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...