SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help create a relationship

    Hi

    I have a situation like this...a visitor to a site takes a test...I ask for

    Surname/Name/DOB/email address.

    A report is given to the visitor and he may decide to buy products..if he
    buys I register him into the system and I ask for personal details as above and address /city as well.

    Now the logic is like this A person may take a test and then buy or he may go directly and buy.On the other hand a person may take a test and decide not to buy.

    All those who did tests and those that purchased ..there details are stored.

    Now I have a test table and a customer table...what sort of relationship can I create here ...will this result is duplicate stores as in personal details..would I need to create another table that can link these 2.

    Any advice is appreciated
    Niva

  2. #2
    _ silver trophy ses5909's Avatar
    Join Date
    Jul 2003
    Location
    NoVa
    Posts
    5,466
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I see at least three tables here:
    customer (stores all personal info)
    test (stores customer id as FK and stores all of the users test answers)
    order(stores customer id as FK and stores the user's order info -- not personal info)

    Keep all of the personal info in the customer table. You can get to any of the tables from all three tables by checking the customer id. Hope that makes sense; im tired
    Sara

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nivashni
    Hi

    I have a situation like this...a visitor to a site takes a test...I ask for

    Surname/Name/DOB/email address.

    A report is given to the visitor and he may decide to buy products..if he
    buys I register him into the system and I ask for personal details as above and address /city as well.

    Now the logic is like this A person may take a test and then buy or he may go directly and buy.On the other hand a person may take a test and decide not to buy.

    All those who did tests and those that purchased ..there details are stored.

    Now I have a test table and a customer table...what sort of relationship can I create here ...will this result is duplicate stores as in personal details..would I need to create another table that can link these 2.

    Any advice is appreciated
    Niva
    create a 1 to many relationship with text (FK) and customer (FK)

    and join them to a users table (PK), those who register would be here and not in the test table. The test table would record only those who took the test.

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ses5909
    I see at least three tables here:
    customer (stores all personal info)
    test (stores customer id as FK and stores all of the users test answers)
    order(stores customer id as FK and stores the user's order info -- not personal info)

    Keep all of the personal info in the customer table. You can get to any of the tables from all three tables by checking the customer id. Hope that makes sense; im tired

    Sorry, ses5909. Was trying to post when i saw yours

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THanks for replying..

    What you are saying makes sense...I have a few challenges though..like any visitor to the site can take a assesment...All I ask is the surname/firstname/dob/email.

    If they buy I ask more information like password/address/gender etc..

    From Market reasearch we noticed that once we started to ask address for
    the test people became reluctant to do the test, hence we reduced the number of fields for the test.So any person that does a test is not necessarily a customer.Now if I store the visitor(assessment personal info) information in the customer table I will have a lot of NULLS...So I guess what I am asking is ,is it ok to store name/dob/email in 2 tables.also can I create a relationship between 2 tables using the email field although in the customer table email is compulsary but not in the test table...another challenge for me..

    Any thoughts please
    Niva

  6. #6
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI Guys

    Just to add more information::

    Any visitor can take as many assessments as he likes...so he can do one test now and repeat in a few hours or even in days...

    Niva

  7. #7
    _ silver trophy ses5909's Avatar
    Join Date
    Jul 2003
    Location
    NoVa
    Posts
    5,466
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Once you gather the few pieces of information for the visitor taking the test, you store that information into the users table:

    insert into users (firstName, lastName, dob, email) values ("jane", "dan", "2005-12-12", "jane@hotmail.com")

    Then once you ask for more information, you will UPDATE the table rather than creating a new insert.

    update users set password = "asdasdas", address = "asdsads, etc = "etc" where user_id = 45;

    where 45 = the id of the user you just inserted.
    or if the email is unique per user you can have that be the where clause.
    Sara

  8. #8
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    USA
    Posts
    633
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    he said email is not compulsary...difficult one for sure..

  9. #9
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys

    Lets say we have 3 tables user/customer/assessment

    Then we have

    user
    user_id
    name
    dob
    email

    customer
    address
    country
    user_id (fk)

    assessment
    assessment_id
    assesment_info
    user_id (fk)


    Now I want to throw something at you guys suppose I change my customer table to:-

    customer
    cust_id
    name
    dob
    email
    address
    country
    user_id (fk)

    Will this be considered bad DB design, I would feel safe to have the personal details in the cutomer table as well, but is it considered bad design..What do the proffessionals think.My problem is :-

    1. a family may have only one email address but all of them can do assessments.

    2. email address is not compulsary for user table.


    Another question is ..can I create relationship with a field that can be NULL as in email in the user table.Although it will not be NULL in the customer table.

    Niva
    Last edited by nivashni; Jan 17, 2006 at 14:07.

  10. #10
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I must have missed it

    Waht database are you using ?

  11. #11
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql...

  12. #12
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, sorry not my areana..........

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    moved to mysql forum

    what is the difference between a user and a customer? couldn't they share a table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy

    Thanks for that..

    A user is any visitor to the site that takes an assessment..The information asked is minimal as we donot want them to go away.So all we ask
    is surname/firstname/dob/email.Email is optional just in case someone does a assessment but does not have or does not want to give their email address.

    If the visitor decides to buy products they become a customer and here we ask them to register into the system...so a vistor can take an assessment and immediately decide to buy.here we need same information as above but also address/city/country/email.Email here is compulsory.Also they would choose a password and that is stored in another table.

    So I guess what I am asking is , is it bad DB design to have name/dob/email etc in 2 tables.

    Also in the visitor table a person can take more that one assessment, and that would create repeating fields.Visitor table is linked to assessment table that actually stores the assessment.

    I have been searching a week to get an answer to this one.

    Any help is appreciated
    Niva

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i would use only one table for both user and customer, and have a status field which is updated when the user buys something
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist
    Join Date
    Mar 2005
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy


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
  •