Jump to content
Larry Ullman's Book Forums


  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About SteveM

  • Rank
  1. 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!
  • Create New...