Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Question about the structure of my database

    Hi guys,

    I've just finished the code of my php database for users to upload photos and add the data to my database. There's one thing about the structure of my db that I don't like though and I don't know how to change it. Currently my structure looks like this:

    username = varchar
    photoname = varchar
    photo_size = int
    photo_type = varchar
    photo_id = int (auto increment)

    The content of my table:

    username | photo_name | photo_size | photo_type | photo_id

    member1 | photo1.jpg | 2352 | image/bmp | 1
    member1 | photo2.jpg | 4822 | image/jpeg | 2
    member2 | photo1.jpg | 12398 | image/jpeg | 3
    member2 | photo2.jpg | 7283 | image/jpeg | 4

    This does work, but I expect it to lead to trouble when my userbase grows. I'd like the result to be like this:

    username | photo_name | photo_size | photo_type | photo_id

    member1 | photo1.jpg | 2352 | image/bmp | 1
    member1 | photo2.jpg | 4822 | image/jpeg | 2
    member2 | photo1.jpg | 12398 | image/jpeg | 1
    member2 | photo2.jpg | 7283 | image/jpeg | 2

    So I want photo_id to start at 1 for the first upload of every new member.

    If it can't be done the way I do it, could anyone give me some pointers on how to do it? Normally I would google this, but I really have no idea what query to use.

    Thanks in advance!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    First question: Why do you want this? Are you trying to keep track of how many images each member has? If so, use a query to find out and ignore the ids. Are you wanting to keep the uploaded images in upload order? If so, your current method works just fine.

    Second question: What do you want to happen if the user uploads 4 images and then wants to delete the second one? Will the next uploaded on be #5 or will it become #2? Of would you renumber #3 and #4 to #2 and #3 when the delete takes place?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi,

    No it's not to keep track of the amount of pictures or anything like that, I use php queries to figure that out. I just thought it would be more logical to do it the way I suggested. I got my current structure up and running and it works good. I'm just wondering if it's suitable for when my database becomes bigger?

    About question 2: I'd just make the next upload #5.

    Thanks for your time!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Then unless you have a burning need for the id by user, I would definitely leave this exactly as it is.

    It will be *MORE* overhead (at least one extra SQL query per added photo) to do the id's by user. Plus the id's would no longer be usable as primary keys in your table. I don't know if you will ever link another table to this one--and maybe you won't but are you sure?--but if you ever do decide to do that, then the id's by user would be useless for the task (well...not quite true...but more work, at least).

    If you have a mistake here, it's in the use of the membername field. That *really* should be changed to memberID where memberID is then the auto_increment primary key field in your master members table.

    And, if you are using INNODB, you would then make memberID in this table a foreign key to that members.memberID primary key.

    And, finally, if you wanted to you could even apply CASCADE DELETE to that foreign key relationship so that if you ever delete a member his/her pictures get deleted automatically.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Question: How are you storing those photos, on disk?

    Are you storing each member's photos in a separate directory? If not, then what do you do when Joe uploads "photo1.jpg" and Mike already has "photo1.jpg" in the directory?

    See, if you kept your id as auto_increment, you could rename all the photos to match the id and then you are guaranteed to never have any name conflicts.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    If you have a mistake here, it's in the use of the membername field. That *really* should be changed to memberID where memberID is then the auto_increment primary key field in your master members table.

    And, if you are using INNODB, you would then make memberID in this table a foreign key to that members.memberID primary key.

    And, finally, if you wanted to you could even apply CASCADE DELETE to that foreign key relationship so that if you ever delete a member his/her pictures get deleted automatically.
    Some real good suggestions here. I'm gonna work on all of them tomorrow. I'm more into PHP than MYSQL, so suggestions like these are really useful to me

    Quote Originally Posted by Old Pedant View Post
    Question: How are you storing those photos, on disk?

    Are you storing each member's photos in a separate directory? If not, then what do you do when Joe uploads "photo1.jpg" and Mike already has "photo1.jpg" in the directory?

    See, if you kept your id as auto_increment, you could rename all the photos to match the id and then you are guaranteed to never have any name conflicts.
    The photo[x].jpg was just an example, I store them as username[x].jpg, so that's not really a problem. I still might decide to use custom directories for every user, although I'm not sure yet.

    Thanks again for your suggestions, I'll work on them tomorrow and I'll let you know if everything goes according to plan!

  • #7
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    If you have a mistake here, it's in the use of the membername field. That *really* should be changed to memberID where memberID is then the auto_increment primary key field in your master members table.

    And, if you are using INNODB, you would then make memberID in this table a foreign key to that members.memberID primary key.
    I'm trying this right now, but not really getting the desired result. I get the error message

    "Cannot add or update a child row: a foreign key constraint fails (`creativetalents`.`uploads`, CONSTRAINT `uploads_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`))"

    I believe this has to do with the fact that userid in my members table is an auto_increment column, while it isn't in my uploads table.

    What am I missing here?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    No, it means you are trying to use a number for userid in the UPLOADS table that does not (yet) exist in the USERS table.

    That's the whole point of a REFERENCES contraint: You can ONLY create records in UPLOADS that reference already-existing USERID values in USERS.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Krtoffel (10-23-2011)

  • #9
    New Coder
    Join Date
    Oct 2011
    Posts
    24
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks again for your response. My knowledge of MYSQL was a bit too limited to get the job done, so I took some time to learn a bit more about it. I managed to figure it out now and see why it's handy to have the relation between the two. Thanks again for your time!


  •  

    Posting Permissions

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