Trying to avoid bad MySQL database design - can't figure out how to avoid too many columns

Question

I am pretty much a beginner having only created a few small php web applications relying on a MySQL database. I am now working on a somewhat larger project. I am in the planning stages of setting up the database and am having difficulty wrapping my mind around one issue.

The database will be used for Lego. The main tables will be parts and sets. Under the sets table I will require a listing of the part numbers that are in each set as well as a listing for the quantity of those parts in the set. My limited knowledge of how to set this up would see me setting up something like... part_1, part_2, part_3... etc & qty_1, qty_2, qty_3... etc. The problem with this is that there will be some sets that have hundreds of parts which makes the method I can think of unfeasibly messy. Also I would like to have the functionality for a user to be able to query the database and recover all sets that have a particular part. With the setup I have in mind I can't see this as possible as it would potentially require a search through 100+ columns.

I am sure there is a simple solution that my noobness cannot see and one which I cannot find online via the keyword searches I have been making (I am 4 hours into that search and have learned a lot but not what I wanted...). Any advice would be very greatly appreciated.

Apologies for not giving you code. The website will be done in PHP with a MySQL database. At the moment I am at the stage of drawings in a notebook and do not have anything coded down.

Answer 1

Anytime you'd start getting a col1, col2, col3 in a database design, you "typically" want to consider those columns as rows in another table. (typically, there are always exceptions - doubtful this is the case ;) )

So you have a LEGO_SETs and LEGO_PARTs table already ?

Sounds like you have something like this:

table: LEGO_SET
SET_ID
name
... etc ..
PART_1
QTY_1
PART_2
QTY_2
... etc.

table: LEGO_PART
PART_ID

You have here a many to many relationship. (ie A Lego_Set has many Lego_parts. A Lego_Part may belong to many Lego_sets).

You should have an interim table to help resolve this: Remove the PART_1, QTY_1, etc. from LEGO_SET table and do this:

table: LEGO_PART_SET
SET_ID
PART_ID
QTY

that's it.

So if you have SET_ID 123, having Lego_parts 333 and 444. You would have 2 rows in this table:

SET_ID  PART_ID  QTY
123     333       12
123     444       4

Hope that helps.

[edit] Edited based on comments: Don't use SET table, avoid reserved words. [/edit]