SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help to code a relational database

    Hi folks!
    I need help how to code or links to tutorials and examples.
    What i want to do:

    I have a temporary table with the following columns:

    `company_id` int(11) NOT NULL AUTO_INCREMENT,
    `company_name` varchar(30) NOT NULL,
    `company_address` varchar(50) NOT NULL,
    `company_zipcode` int(6) NOT NULL,
    `company_city` varchar(30) NOT NULL,
    `company_telephone` varchar(20) NOT NULL,
    `company_addressid` int(9) NOT NULL,
    `person_firstname` varchar(20) NOT NULL,
    `person_lastname` varchar(20) NOT NULL,
    `person_email` varchar(30) NOT NULL,
    `person_emailstatus` varchar(12) NOT NULL,
    `person_position` varchar(50) NOT NULL,
    `answer_1` varchar(50) NOT NULL,
    `answer_2` varchar(50) NOT NULL,
    `answer_3` varchar(50) NOT NULL


    Then i have 3 tables:
    company
    `company_id` int(11) NOT NULL AUTO_INCREMENT,
    `company_name` varchar(30) NOT NULL,
    `company_address` varchar(50) NOT NULL,
    `company_zipcode` int(6) NOT NULL,
    `company_city` varchar(30) NOT NULL,
    `company_telephone` varchar(20) NOT NULL,
    `company_addressid` int(9) NOT NULL

    person
    `person_id` int(11) NOT NULL AUTO_INCREMENT,
    `person_firstname` varchar(20) NOT NULL,
    `person_lastname` varchar(20) NOT NULL,
    `person_email` varchar(30) NOT NULL,
    `person_emailstatus` varchar(12) NOT NULL,
    `person_position` varchar(50) NOT NULL

    answer
    `answer_id` int(11) NOT NULL AUTO_INCREMENT,
    `answer_1` varchar(50) NOT NULL,
    `answer_2` varchar(50) NOT NULL,
    `answer_3` varchar(50) NOT NULL,


    The problem:
    I want to insert from the temporary table to this 3 tables were i need it to be relational so in the table company one company only can be one time ( the company_addressid is a unique number for that company ) so when i insert to that table it should match this company_addressid anf if exists it only should update the data

    when insert to person it should be the same as company but in this case the person_email is the unique data to match from so if exists only update the record

    when insert to answer it should be the same as person but in this case if the person has already one answer attached to him it should only update the record

    Solution:
    I think i have to have the following extra columns:
    In person:
    company_id

    In answer:
    person_id


    Hope someone can help me in the right direction how to select from the temporary table and split it to this 3 tables and still avoid duplicates in the company, person, answer

    If you have some example please post it

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2008
    Location
    The Netherlands
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    See if I understand. 1 company has a relation with 1 person. 1 person has a relation with 1 question.
    Or can 1 company have a relation with multiple persons and a person can have a relation with one company.
    Can you explain it a bit more for me.

    Are you familiair with 1 to many (1:n), many to many (n:n) or 1:1 relations?
    In case of 1:n relation or 1:1 relation you can indeed add columns you suggested.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jackthecute View Post
    Worrying about keywords from the start is a bad idea.
    perhaps you did not realize which forum this thread is in

    because your reply has absolutely nothing to do with the question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •