0

This is a problem I've had for a while and not sure what is the best way to solve it. Its probably easier for me to explain using an example.

So I have many collections and many items. Some items can belong to one or more collections or none at all. Collections must have at least one item but there is no limit to the number of items that it can have.

How do I store this information in a MySQL database / what is the most efficient way of doing it?

I already have a table of items with a unique ID per item, thats the easy part, creating the collections part is where I'm stuck.

Do I create a new table for every collection, give it a unique table name and insert new records for each item that belongs in that collection?

Do I create just one large collection table with each record being a new collection and a single field of all the item ids?

Is there another way of doing it?

This will be queried using a php site if that makes any difference to the database structure.

Improve this question

1 Answer 1

Reset to default
2

I think you should..

Create a collection table with all collections, an item table with all items, and a collection-item map table which will have id(primary key), collection_id (foreign key to collection table), item_id(foreign key to item table). This table will represent the relation between a collection and item. It can have any number of collection item relation.

Hope this helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Great, that sounds perfect. Thanks for the quick response.
You may then want to look into joins in SQL as then you can combine the two tables
Had to wait 5 mins before i could accept, its done now

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.