SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Relational Database

    Hi,

    Anyone here has a suggestions on what to do on this tasks:

    1. I want to make a form which has 30 or more fields that our agents will fill in. These are Personal Info, Address and Housing Info, Employment Info, Security Info and Additiona cards info. Now as of the moment, These five categories are being saved in a one table for the query purposes that it is easy to fetch up (i'm a newbie and don't know how to segregate from one to five tables). I'm planning to resetup the database and segregate these five categories into 5 tables also. Now, my question is: these five categories are in one form or page. In a page the format of my web is on below. Now, if agents will click SUBMIT button of course all data will be encoded to the database on each corresponding table. But how I would be able to get or query those data in a one form. If it needs a lookup table, how would I implement that one.

    Please help me.. really need your inputs on this matter. Thank you so much....

    I. Personal Info:
    Firstname: __________ Middlename:_________ Lastname: _______
    Homephone:
    email address:

    II. Address info and house info:
    street:_________ city:_______ state:_______ zip:_______
    House status:________ House type:____________

    III. Employer info:
    name:_______________ occupation:___________
    salary: ________________
    IV. Security info:
    Date of Birth:_________ SSN:_________ Mother's Maiden Name:_______

    V. Additional Cards Info:
    Firstname:________ Middlename:_________ Lastname:_________

    (SUBMIT Button)

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You create a new table based on functional dependencies, splitting off things that do not really depend on the person's ID number for example.

    Apart from the Additional cards info, which is a duplication, if person has one home address and one employer, then all this info is functionally dependant on the person's ID number (this ignores the zip code bit where by parts of the address are encoded in the zip code, but most people ignore that anyway.)

    So I think it is all a single table.

    Someone being very exact would say that several people can live at the same address, so you could have an address with an addressID and then a person_address table with personID and addressId in it.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi John,

    Thanks for your comment... It is really appreciated...

    So, there is no other way to segregate the data into each corresponding categories? And according to your suggestion, it is only the personal and address would probably could split up into two. But I cannot picture out on how I will split those, since those categories are being inputted by agents in a single form. Once the agent click on SUBMIT those datas will be encoded in the database at the same time. I'm not 100% sure if It gets the same id number, let say personal is the same id with address so that once it will fetch I got the correct info.

    By the way, on real situation like yahoo registration mail. How would they segregate the info? or they will design it in a single table?

    Thanks in advance....

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no idea how yahoo does it, but you should do it with one table

    the fact that two people might live at the same address is inconsequential
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys,

    Thanks for your idea.... it is indeed a great help for my project.....

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi if (r937 || DR JOHN);

    I have seen a command from mysql which is mysql_insert_id(); in that function returns the number assigned to the last-inserted entry by the AUTO_INCREMENT feature in MySQL.

    That way, I may have split the five categories into 5 tables and it will get their respective ids by that functions and save it on the lookup table. In inquiry, I will have to fetch the data being encoded on the lookup table first then fetch the info to its corresponding id since it AUTO_INCREMENTAL....

    Does it make sense? Does it make serve my purpose?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, doesn't make sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    why do you insist on separating this in to five tables? just because you have the "sections" numbered with roman numerals doesn't mean that you need five sections.

  9. #9
    SitePoint Member
    Join Date
    Feb 2007
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Writing meaningful facts get you the design

    The trick is in how well you can articulate the facts pertaining to your problem
    description.

    Just don’t think about tables. Just think about writing atomic facts like the
    following ones. From the nouns of these facts, you can easily decide the
    tables, their attributes and also foreign keys. The clearer and explicitly you
    articulate your atomic facts, clearer and elegant is your database design.

    A person has first name
    A person has middle name
    A person has last name
    A person has email address
    A person has date of birth
    A person has SSN
    A person has mother-maiden-name
    A person has house
    A person has employment


    A house has status
    A house a type
    A house has address

    Address has street
    Address has street
    Address has city
    Address has state
    Address has zip

    Employment has employer
    Employment has salary
    Employer has a name
    Employment has salary


    It is all about your meaningful and simple sentences. If you have alternative
    ways of articulating them, I would like to see. Even then you shall see
    beautiful design emerging.
    Last edited by r937; Feb 17, 2007 at 00:53. Reason: link removed


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
  •