MySQL Database Design Question

  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

So I have a table with user information such as email, username, etc. as well as a user_id auto_inc primary key

I also have a table that lists a little over a hundred items.

I want to record how many times a user has destroyed each of these items, as well as how many times a user has created each of these items.

So the result would look something like this:

Code: [ Select ]

User Table:
_________________________
| user_id |  username  |
----------------------
| 0     | username12 |
| 1     | username4  |
----------------------

Join table?
__________________________________________
| user_id | item  | created | destroyed |
------------------------------------
| 0     | item1 | 20     | 5        |
| 0     | item2 | 40     | 3        |
| 0     | item3 | 13     | 10       |
| 1     | item1 | 12     | 12       |
| 1     | item2 | 1      | 5        |
| 1     | item3 | 16     | 11       |
------------------------------------

Item Table:
________________________
| item_id | item_name |
--------------------
| 0     | item1     |
| 1     | item2     |
| 2     | item3     |
--------------------
  1. User Table:
  2. _________________________
  3. | user_id |  username  |
  4. ----------------------
  5. | 0     | username12 |
  6. | 1     | username4  |
  7. ----------------------
  8. Join table?
  9. __________________________________________
  10. | user_id | item  | created | destroyed |
  11. ------------------------------------
  12. | 0     | item1 | 20     | 5        |
  13. | 0     | item2 | 40     | 3        |
  14. | 0     | item3 | 13     | 10       |
  15. | 1     | item1 | 12     | 12       |
  16. | 1     | item2 | 1      | 5        |
  17. | 1     | item3 | 16     | 11       |
  18. ------------------------------------
  19. Item Table:
  20. ________________________
  21. | item_id | item_name |
  22. --------------------
  23. | 0     | item1     |
  24. | 1     | item2     |
  25. | 2     | item3     |
  26. --------------------


Is a join table appropriate for this kind of thing, or should I be going about it a different way. If I should be using a join table...how do I go about doing it when it holds more information than simply the item_id and user_id?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

Anyone?
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Kind of difficult to understand here. For your item table, are each of those going to be unique per user? Or is it possible that an item could be shared amongst users? Also lets say a user creates an item, destroys an item, and then creates an item. Is it possible with how you are doing things that the same item could be created again, or would that be unique in the table?

Depending on how you answer there are different ways you can go about this I think.
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

all users will have a record of all items.
Item 1 can be created any number of times, and destroyed any number of times by the owner of the item.

There is an instance of item 1 for each user. Each user can create or destroy their instance of item 1 as many times as they want. Items have set names, but are not shared among users. However user 1 and user 2 can both have an instance of item 1.

There won't be an individual record for each instance of item 1, instead, each user will have 1 record per item, and that record will be updated to display the new numbers for destroyed and created. destroyed and created can only count up, they can't go down.

Hope that covers all the logistics...if I missed something let me know - could really use the help on figure this on out.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Okay I think I understand better now.

I think I would have started it the same way as you. First you would have your user table, and then you would have your item table with all of your items.

For your join table, I think that is the route I would also take here. You don't need to put the item name again though, just reference the item id as that would be more efficient. So it would be:

Code: [ Select ]
__________________________________________
| user_id | item_id | created | destroyed |
------------------------------------
| 0    | 1    | 20   | 5    |
| 0    | 2    | 40   | 3    |
| 0    | 3    | 13   | 10   |
| 1    | 1    | 12   | 12   |
| 1    | 2    | 1    | 5    |
| 1    | 3    | 16   | 11   |
-------------------------------------------
  1. __________________________________________
  2. | user_id | item_id | created | destroyed |
  3. ------------------------------------
  4. | 0    | 1    | 20   | 5    |
  5. | 0    | 2    | 40   | 3    |
  6. | 0    | 3    | 13   | 10   |
  7. | 1    | 1    | 12   | 12   |
  8. | 1    | 2    | 1    | 5    |
  9. | 1    | 3    | 16   | 11   |
  10. -------------------------------------------


Then you can create as many elements as you need there for that association between a user_id and an item_id, so you currently have the created and destroyed info, and you could add other info if you wanted that would be unique to any user_id plus item_id.

So you pretty much had it already, unless I am missing something. On this table I would set a unique key being the user_id and item_id together, as I don't think you would have any cases where a user_id would have more than one of the same item_id here. Most of your lookups would probably be using that index too.
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

okay cool. Just wanted to make sure I had the right idea.

There are several places where I'll be using this structure on the database so I was wondering if you would mind giving me an example query of how to select all items that a user has destroyed / created including the item name and the user name? I'm not much of a SQl genious but I learn from example pretty quickly.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

Something like this should do:

SQL Code: [ Select ]
SELECT u.username, i.item_name, j.created, j.destroyed
FROM user_table u, item_table i, join_table j
WHERE u.username = 'Frank'
AND j.user_id = u.user_id
AND j.item_id = i.item_id
  1. SELECT u.username, i.item_name, j.created, j.destroyed
  2. FROM user_table u, item_table i, join_table j
  3. WHERE u.username = 'Frank'
  4. AND j.user_id = u.user_id
  5. AND j.item_id = i.item_id
  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

awesome. Thanks a ton BWM!

Post Information

  • Total Posts in this topic: 8 posts
  • Users browsing this forum: No registered users and 81 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.