SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question Manay to Many Relationship

    Howdy! This is a 2-part question:

    There's this form that is created to collect a variety of information (4 pages):
    HTML Code:
    Page 1:
    email:
    password:
    
    Page 2:
    company name:
    first name:
    last name:
    address:
    state:
    zip:
    phone:
    
    Page 3:
    project name:
    is this a new site: (check box) Yes   (check box) No
    if no, current URL:
    if yes, desired URL:
    
    Page 4:
    what kind of pages would you like: (check box) Home   (check box) Contact   (check box) Sitemap   (check box)  Feedback  and so on
    other:
    This information is then passed on to another form that will display and calculate an estimate on a web page.

    I'm guessing I'm going to have a many-many relationship database, but I don't know how to store the multiple-checked values (the ones that the customer has clicked) in the customer's data table.

    HTML Code:
    Theoretical tables:
    Table: rsClient
    Field: rs_id
    Field: fname
    Field: lname
    Field: company_name
    Field: address
    Field: city
    Field: state
    Field: zip
    Field: phone
    Field: email
    
    Table: pwds
    Field: pwds_id
    Field: pwd
    
    Table: rsProject
    Field: project_id
    Field: projectName
    Field: curSite
    Field: cureURL
    Field: desiredURL
    
    Table: rsPages (optional text they want to add)
    Field: pages_id
    Field: other
    
    Table: rsOptions (this table would have the id and list of optional values)
    Field: option_id
    Field: option (i.e., contact, home, SEO, etc.)
    Field: cost
    
    Table: rsEstimate (this would be the info. collected and retrieved (in combination with the other tables) to display on the estimate page)
    Field: rs_id
    Field: pwds_id
    Field: project_id
    Field: pages_id
    Field: option_id
    Some ideas would be appreciated!
    Thank you for your time!

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Relational databases don't generally support many-to-many relationships. The time honoured method for handling such situations is to have a third, linking table.

    e.g.
    A school subject is taught to many pupils, and a pupil may attend several classes, typical many-to-many.

    Tables
    Code:
       PUPIL
       SUBJECT
       PUPIL_SUBJECT
    PUPIL data
    Code:
    ID NAME
    1  Dave
    2  Mark
    3  Linda
    SUBJECT data
    Code:
    ID TOPIC
    1  Math
    2  English
    3  Computer Science
    PUPIL_SUBJECT
    Code:
    PUPIL  SUBJECT
    1        1
    1        3
    2        1
    2        2
    2        3
    3        1
    3        2
    Dave attends classes in Math and Computer Science, Mark attends classes in all 3 subjects, Linda studies Math and English
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Usually once you get the whole thing worked out properly you find that there are additional fields that belong to that extra table. In the pupil/subject example the final grade that a pupil got for the subject would be an obvious example of a field to add to that third table when you work out that you need to store that value.

    There are not any many to many tables in the OP's database structure as the rsEstimate serves the purpose of being the linking table between any two of the others.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    819
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile

    I see that I'll need one final column within the rsEstimate for the 'estimated total' of all of the cost of options chosen from rsOptions.

    So what I have proposed would be considered a one-to-many then?

  5. #5
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Yes.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


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
  •