I've never used MySQL before, and I'm wondering if I could use it for the following task:
I am making a website where people can go to it and basically submit a shopping list. They will give the list a Name such as "Sue's List" and then they can enter 1-15 items, and for each item they enter 4 standard bits of data which can be applied to any item IE:
1)Name of item
4)Description of Item
From there I want to have another webpage which is capable of displaying all the lists that have been created, and when clicked, it will then show the items in that list.
Since MySQL is not made to create a hierarchal set of objects, is it possible to do this.
Three possible work arounds that I was thinking about would be to either A)Create a new table for each shopping list that is created, and then a master table for these subtables. Is there a limit to the number of tables that can be created though? If 100 different people create a list, then it may get pretty cluttered with tables. Also, is there even a way to store a reference to a table as an element in another table
B)Create a table of all the items from all the shopping lists and give each a reference number, then create a master table of the shopping list names IE "Sue's List" and a list of the associated reference numbers
C)Create one table where each element has 61 Fields, where the fields are:
1)Name of List
2)Item 1 Name
3)Item 1 Cost
4)Item 1 Quantity
5)Item 1 Description
6)Item 2 Name
61)Item 15 Description
and if there are only, say, 2 items in the shopping list, then the rest of the fields are left null--It seems really inefficient to me though.
Would it be possible to do this in MySQL. If so, which method would be best, or is there a better method altogether? Is there something better to do this in than MySQL?