SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    0 Post(s)
    0 Thread(s)

    Lightbulb DB theory and design for associations

    I am new to php and MySQL as well as web design as far as that goes. Thru some books, one of them being "Build Your Own Databse Driven Website", and some online help I have put together a script that will upload multiples files into a directory and enter the appropriet info in to a db. Having completed a working form , I now want to add more info into the form. I want to add things like name, address, phone, etc. What is the proper way to setup the db to handle this new info? Each record that will contain name, address, phone, etc will need to be associated with several uploades files. I currently have a table called "uploads" in my db which stores the info about the individual files that get uploaded. Do I just add more fields to it, Or do I create a new table? Either way how will I make the associations. I may have rambled a bit, sorry for that. I am a little over my head and strugling to understand the theory as well as the practice of it all, but i am determined to get thru it. Thanks in advance.

    I don't know if it makes any difference but, I wanted to mention that the number of files that will be associated with any one record could be 2-3 or as many as 10-12 or more. This number could be different for each record and will change as new documents get scanned and entered into the db.


  2. #2
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    7 Post(s)
    0 Thread(s)
    I would use 2 tables for this, say one is called "people" and the other "files". "People" will have the following columns:

    person_id INT UNSIGNED
    name VARCHAR(255)
    street VARCHAR(255)
    city VARCHAR(255)
    phone VARCHAR(100)
    ...etc. - you can add new columns at any time later on if you need to

    And "files":
    file_id INT UNSIGNED
    filename VARCHAR(100)
    attached_to INT UNSIGNED

    Create PRIMARY KEY on person_id and file_id, you can also make them AUTO_INCREMENT for convenience. Also create a KEY on attached_to. Now, person_id and file_id will be unique identifiers for each row of the tables. Each row in "people" will contain information about one person entered in your form. Each row in "files" will contain information about every single file you upload through the form. "attached_to" will have information about which person this file belongs to, ie. it will have the "person_id" value of the appropriate row in "people" table. In this way you can have as many files belonging to one person (entry) as you wish. Then, if you need to know which files are associated with a person, you just do this query:

    $result=mysql_query("SELECT filename FROM files WHERE attached_to=$person_id");

    This query will perform very fast because you have created a key on "attached_to". I hope this is all clear to you and that I've understood what you want to achieve.


Posting Permissions

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