locked
Database Relationship or Design Recommendation RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,
    I have four tables in my database project. One of them is a shared table. Sometimes other tables will be use this table. So I call it "shared".


    TablePersonal           -          TableJobs            -            TableProjects

                                                         | |
                                                         \/

                             TableReminders (aka shared table)


    There should a one-to-many relationship between this shared table and others. But if I create one-to-many relationships for all of them, there may be conflicts in database. More clearly:
     
    -TablePersonal       -TableJobs        -TableProjects    -TableReminders
     PersonalID (PK)       JobID (PK)           ProjectID (PK)       ReminderID (PK)
     Name                     Name                   Name                   ForeignID (FK)
     

    So If all PKs (primary keys) are related to ForeingID field of TableReminders, there may be conflicts because of same ID values. Simply, all PKs may have same ID value (For example TablePersonal's ID field has "15", but other tables may have it, too). So DB will give an error about conflict on ForeignID field.

    This method doesn't work maybe you can say it is totally wrong. Anyway I'm not saying "there must be one reminder table or shared table". I can create reminder tables for all of these three tables. (For example, TablePersonalReminders, TableJobsReminders, etc...) But I don't know this is the right method. Any suggestions?
    Sunday, November 8, 2009 12:16 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Justin,


    I would lean towards my second suggestion from that thread - creating a PersonalReminders table, a ProjectsReminders table, a JobsReminders table, and a Reminders view that will perform a unioned SELECT on each of the reminders table.

    My first option would also work, but if you create a new Reminder type, you'll need to modify the table, update the check constraints, etc.  In the second solution, all you need to do is modify the view to add an extra unioned SELECT.
    • Marked as answer by Justin.NET Monday, November 9, 2009 1:28 PM
    • Marked as answer by Justin.NET Monday, November 9, 2009 1:28 PM
    Monday, November 9, 2009 4:00 AM
    Answerer