locked
Database Relationship RRS feed

  • Question

  • User931778073 posted

    Hi all, I have a question regarding populating database tables. Suppose I have a total of 3 tables in my database namely User table, Skills table, and Education table. The User table has a primary key called ID, the Skills table has 5 fields plus a SkillID field and a primary key called ID, and the Education table has 5 fields plus an EducationID field and a  primary key called ID. Both the SkillsID and the EducationID fields are linked to the Primary key of the User table in a one to many relationship.    

                      User Table
             |--------------------------|
             |              ID----------|---PK
             |        UserName          |   /\
             |--------------------------|  /  \
                                          /    \
                   Skills Table          /      \          Education Table
            |-------------------|       /        \         |----------------------|
       PK---|---------------ID  |      /          \        |            ID -------|----PK
            |          SkillID--|-----/            \-------|-- EducationID        |
            |-------------------|                          |----------------------| 

    The question is when a user sends information to populate the Skills table or Education table, must the ID from the User table aslso be sent? The person who built this database told me that it is not neccessary, these two tables will automatically know which ID from the User table the information are coming from. I just don't see how that could happen. Please explain if that is possible, if not what are my alternatives, thanks in advance for your help.

    Sunday, September 2, 2012 3:59 PM

Answers

  • User-1407477457 posted

    Hi thanks for replying. Why would this be a many to many relationship?

    A person can have many skills.  Each skill can be had by many people.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 2, 2012 9:45 PM

All replies

  • User-1199946673 posted

    To start with, the skillID and EducationID (Foreign Key) fields should better be renamed to UserID, because they are related to the User Table,

    And to answer your question, Yes, you need to sent the ID of the User to both the Skills and Education tables as well, how else would your database know to which user the Skill/Education belongs to....

    http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

    Sunday, September 2, 2012 6:11 PM
  • User-1407477457 posted

    I think a many to many relationship is more appropriate.  If you have trouble wrapping your head around database design principles, I've heard good things about the book, Database Design for Mere Mortals.

    Sunday, September 2, 2012 6:27 PM
  • User931778073 posted

    Hi  thank you all for replying. I will have my friend change the table structure.

    Sunday, September 2, 2012 7:14 PM
  • User931778073 posted

    Hi thanks for replying. Why would this be a many to many relationship?

    Sunday, September 2, 2012 7:20 PM
  • User-1407477457 posted

    Hi thanks for replying. Why would this be a many to many relationship?

    A person can have many skills.  Each skill can be had by many people.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 2, 2012 9:45 PM
  • User3866881 posted

    The question is when a user sends information to populate the Skills table or Education table, must the ID from the User table aslso be sent?

    Hi,

    I think so ——because EducationID is the foreign key mapping to ID of the table User Table.

    Monday, September 3, 2012 10:42 PM