Results 1 to 10 of 10

Thread: How should I set up my database???

  1. #1
    Join Date
    Aug 2008
    Location
    Smiths, AL
    Posts
    164
    Thanks
    30
    Thanked 5 Times in 5 Posts

    Default How should I set up my database???

    right now I have my table set up as 3 separate tables, <users>,<images>and <challenges>.

    <users> table has these fields (in this order) Does order matter???
    user_d
    username
    first_name
    last_name
    password
    reg_date
    email
    website
    location
    show_email
    last_login
    create_ts
    gender
    address
    city
    state
    zip
    phone
    country

    <images>
    image_id

    <challenges>
    challenge_id

    What I am looking for is I want users to upload images to their portfolio and/or challenges and have it associate it with the user id.
    So I need to know if my tables are ok or should I make changes? Images entered in challenges should know that they are in a challenge and images should know they belong to a user.

    Anyone have any smart suggests as to how I should make this. I want to make it as simple as I can so I don't cut the same grass twice as they say.

  2. #2
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    <users> table has these fields (in this order) Does order matter???
    order doesn't matter

    What I am looking for is I want users to upload images to their portfolio and/or challenges and have it associate it with the user id.
    So I need to know if my tables are ok or should I make changes? Images entered in challenges should know that they are in a challenge and images should know they belong to a user.
    you would need a unique identifier for the users first... typically its just an auto incrementing field.

    then in your images you would need a unique id (which you have) for the image, BUT you also need to associate that image to the user so you would need a foreign key that contained the user id of the submitter

    then in your challenges table you need the challenge id (which you have) and you would need the image id (which you do not) Now you do not need this table because you already know what user it is by looking at the image table.

    so your basic structure would look something like this (without the additional field detailing specifics about each table)

    <users>
    user_id PK

    <images>
    image_id PK
    user_id FK

    <challenges>
    challenge_id PK
    image_id FK

    and you would associate them somewhat like

    images -> user
    Code:
    SELECT users.field, images.field FROM images LEFT JOIN users ON users.user_id=images.user_id WHERE condition
    challenge -> image -> user
    Code:
    SELET challenges.field, images.field, users.field FROM challenges LEFT JOIN images ON challenges.image_id=images.image_id LEFT JOIN users.user_id=images.user_id WHERE condition
    Those queries will work with using any of the tables declared first in the FROM statement, however I personally use the table that is my main focus first, then I JOIN the others as necessary. Also another syntax trend I practice that doesn't technically make a difference, but is easier for me debugging, is that whatever table I am JOINing I put first in statement and then I put the second table after... That may sound confusing, so using my first example...
    Code:
    FROM images LEFT JOIN users ON users.user_id=images.user_id
    could technically be re-written as
    Code:
    FROM images LEFT JOIN users ON images.user_id=users.user_id

  3. #3
    Join Date
    Aug 2008
    Location
    Smiths, AL
    Posts
    164
    Thanks
    30
    Thanked 5 Times in 5 Posts

    Default

    okay I have some other questions..


    Because I already have the DB set up when I go to add the new fields to the tables will they automatically match up?? For example I already have my user_id on auto_inc but when I create user_id in the images table will the numbers match?

    I have 11 names in my users table already...

    And the codes, i will put in my php pages not in MySQL area somewhere right?
    anytime I write data to the db I use the codes above to write to all the fields??

  4. #4
    Join Date
    Aug 2008
    Location
    Smiths, AL
    Posts
    164
    Thanks
    30
    Thanked 5 Times in 5 Posts

    Default

    Does "UNIQUE" and foreign key mean the same thing?

  5. #5
    Join Date
    Aug 2008
    Location
    Smiths, AL
    Posts
    164
    Thanks
    30
    Thanked 5 Times in 5 Posts

    Default

    I don't know how to get the tables to see each other, please someone help me

  6. #6
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    Quote Originally Posted by Dirt_Diver View Post
    Does "UNIQUE" and foreign key mean the same thing?
    No,
    UNIQUE means that the value contained within this field cannot appear anywhere else in this table.
    foreign key is a reference to a primary key; the primary key field must be a unique value, however its possible to have multiple fields in a table that have the same foreign key.

    EG
    <users>
    id|fname |lname
    1 |John |Smith
    2 |Jane |Smith
    3 |Michael |Jordan
    4 |William |Clinton
    5 |Monica |Lewinski

    <house>
    house_id |address |phone
    10 |One Maple Dr. |(555)555-5555
    20 |1600 Penn Ave. |(111)111-1111

    <house_occupants>
    house_id|member_id|status
    10 |1 |father
    10 |2 |mother
    20 |3 |landlord
    20 |4 |rentee
    20 |5 |girlfriend

    house id is a foreign key referencing the unique identifier of the specific house, and the member id is the unique identifier of the specific occupant

    so the house id and the member id are foreign keys in the house occupants table, but they are both primary keys in their respective table.

    Setting up the Keys!
    when you set up a uniquely identified database field, set it up so that its an not null unsigned auto-increment field... if you have a front end client, usually there are check boxes or drop downs, but if you are doing it in a command line type environment you would run this statement

    Code:
    DROP TABLE IF EXISTS table;
    CREATE TABLE table (
        unique_field INT(16) NOT NULL AUTO-INCREMENT,
        field OPTION(S),
        field OPTION(S),
        field OPTION(S)
    );
    or if you already have a table, and just wanted to add the unique field by command line interface (cli) you could execute this statement

    Code:
    ALTER table ADD COLUMN field OPTION(S)
    to create a foreign key and associate it with another table/field use

    Code:
    ALTER TABLE table ADD FOREIGN KEY field REFERENCES table(field)
    Quote Originally Posted by Dirt_Diver View Post
    I don't know how to get the tables to see each other, please someone help me
    that is done through the query that I showed you in my first post... you simply reference what two fields must be the same. a more generic query than the one i posted before would be

    Code:
    SELECT tb1.field, tbl2.field FROM table1 AS tbl1 LEFT JOIN table2 AS tbl2 ON tbl1.field=tbl2.field WHERE condition
    if that is confusing... go to www.mysql.com and search for JOIN or use google and search for MYSQL JOIN and just read through some other tutorials

  7. #7
    Join Date
    Aug 2008
    Location
    Smiths, AL
    Posts
    164
    Thanks
    30
    Thanked 5 Times in 5 Posts

    Default

    okay I just read some how to's and still don't get it.

    Here is what I tried what is wrong.

    Code:
    SELECT images.user_id, users.user_id 
    FROM users, images  
    WHERE images.user_id = users.user_id
    and I get this message.
    MySQL returned an empty result set (i.e. zero rows). (Query took 0.0005 sec)

    thinking it didn't do anything I tried this

    Code:
    SELECT images.user_id, users.user_id
    FROM images
    AS user_id 
    LEFT JOIN users 
    AS user_id 
    ON users.user_id=images.user_id 
    WHERE images.user_id = users.user_id
    And it errors out. This looks logical to me... Not sure what is going on.

    I am using MySQLAdmin and if you could just write me a copy and paste code I would understand what you did by looking at it. I don't know how to set the FK... Please help me with this I don't understand it even after reading and pulling my hair out. ??Someone??

    Here's my stuff as it is set up now

    <images>
    user_id (pk) UNSIGNED, Not Null, auto_increment

    <images>
    image_id (pk) UNSIGNED, Not Null, auto_increment
    user_id

    <challenges>
    challenge_id (pk) UNSIGNED, Not Null, auto_increment
    image_id

  8. #8
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    SELECT images.user_id, users.user_id
    FROM images
    AS user_id
    LEFT JOIN users
    AS user_id
    ON users.user_id=images.user_id
    WHERE images.user_id = users.user_id
    should be
    Code:
    SELECT img.user_id, usr.user_id
    FROM images AS img 
    LEFT JOIN users AS usr 
    ON usr.user_id=img.user_id 
    WHERE img.user_id = usr.user_id
    in the process of joining the tables, you were actually renaming both tables to the same name, which cannot be done. so I changed the name of the tables to a different short hand, then i carried that through the rest of the query... however you could have left out the shorthand, and you would have received this query

    Code:
    SELECT images.user_id, users.user_id
    FROM images
    LEFT JOIN users
    ON users.user_id=images.user_id 
    WHERE images.user_id = users.user_id
    that would still error out though, because both fields are named the same thing, so one of them you would need to create a shorthand.

    Code:
    SELECT images.user_id, users.user_id AS usr_user_id
    FROM images
    LEFT JOIN users
    ON users.user_id=images.user_id 
    WHERE images.user_id = users.user_id
    and the last bit of this, is that you do not need to JOIN the tables twice... I am not sure if this causes an error or not, but it definitely isnt necessary. The JOIN clause is meant to be the way to associate and "bring in" additional tables, and the WHERE clause is supposed to limit the results... so something like

    Code:
    ... FROM tbl1 JOIN tbl2 ON tbl1.__=tbl2.__
    would grab every result where the fields from table1 and table2 matched

    where as
    Code:
    ... FROM tbl1 JOIN tbl2 ON tbl1.__=tbl2.__ WHERE tbl1.id>100
    would grab every result where the fields from table1 and table2 matched AND where the id on table1 was less than 100

    so in the second statement, I am putting that 1 extra limitation. so to return to your query, If you would like every image in the database, then you can just delete the WHERE clause, however if you wanted to limit it to a specific user... like say grab every image this user has uploaded to display on their portfolio page or whatever, you would include the WHERE and populate it with the user id of that person

    all images
    [CODE]
    Code:
    SELECT images.user_id, users.user_id AS usr_user_id
    FROM images
    LEFT JOIN users
    ON users.user_id=images.user_id
    images from 1 person
    Code:
    SELECT images.user_id, users.user_id AS usr_user_id
    FROM images
    LEFT JOIN users
    ON users.user_id=images.user_id 
    WHERE users.user_id=__

  9. #9
    Join Date
    Aug 2008
    Location
    Smiths, AL
    Posts
    164
    Thanks
    30
    Thanked 5 Times in 5 Posts

    Default

    I am getting the feeling that I need to add all this to my PHP pages now and not the SQL text box in my MYSQLAdmin right?

    Sorry to keep bothering you.. And BTW I understood all that above just want to make sure I put it in the right spot to get it all to work.

  10. #10
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    you might be able to create a procedure in your front-end client than just call the procedure when you want to run it, however, typically all queries are declared at the point of necessity, so I would say that yes, you need to update all of your pages that you wish to include multiple tables?

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
  •