best practices for storing this info?

[thewood68]

ok so lets say i have database that is storing different kinds of writing utencils (pens, markers, crayons, pencils etc...) and these are all user generated. after that they pick what they like using this utencil best for. example: crayons - coloring. pencils -math etc. but then someone else says they really like using pencils for drawing and ten people agree that they like using it for drawing but 100 say they like using it for math. i need to be able to store all the different uses for each item and how many agree with the use for that. whats the best way about doing that?

[priti]

how about creating a poll for utensils???

 

just an idea

[Psycho]

Four tables. Here are the four with some minimum information.

 

Users Table

UserID | UserName

 

Utensils Table

UtensileID | UtensileName

 

Uses Table

UseID | UseName

 

Utensil_Uses

UtensileID | UseID | UserID

 

The first three tables should be self explanatory. The fourth table would hold records for each user's preference for a utensile. Each of those three columns are foreign keys back to the other three tables. You would want to include logic on the relevant pages to ensure a user cannot enter more than one preference for any particular utensil.

 

[thewood68]

its the 4th table that you speak of im not familiar with on how to set up.

[Psycho]

its the 4th table that you speak of im not familiar with on how to set up.

 

The set up is easy. You simply have three columns that should have the same "specs" as the ID fields from the other three tables - except they won't be auto-incremented. Let's say you have the following information in the first three tables:

(Note the ids don't have to be different for the data between tables I'm only doing it here for illustrative purposes)

 

Users Table

1 | Bob

2 | Max

 

Utensils Table

4 | Pencil

5 | Crayon

 

Uses Table

7 | Drawing

8 | Coloring

 

If Bob was to indicate that he felt Pencil was best for drawing and crayons were best for coloring there would be the following records in the fourth table:

 

Utensil_Uses

UtensileID | UseID | UserID

1 | 4 | 7

1 | 5 | 8

 

If Max indicated that crayons were best for drawing and pencils were best for coloring there would be the folloiwing two records:

2 | 4 | 8

2 | 5 | 7

 

 

Now, the part which will be difficult for you is working with all of this since I am assuming you have never worked with JOINS when workign with a database. I suggest you find a tutorial on working with JOINS before starting. But, here is a sample:

 

Let's say you are on the page for Pencils and you wanted to display each user and their preference for that utencil. You could do a query like this:

 

SELECT Users.name, Uses.UseName

FROM users

  LEFT JOIN Utensil_Uses ON Users.Userid = Utensil_Uses.UserID

  LEFT JOIN Uses ON Utensil_Uses.UseID = Uses.UseID

  LEFT JOIN Utencils ON Utensil_Uses.UtencilID = Utencils.UtencilID

WHERE UtencilID = 4

 

That would return the following data:

Bob | Drawing

Max | Coloring