Database design question (unknown number of columns?)

Question

I am just starting my journey into learning databases/SQL and the like. I decided to make some databases for one of the companies I work for as practice and to help us organize a little.

One of the tables will simply contain all of the individual parts we have in our inventory with columns like: PartNumber, Description, and StockQty.

The other table would contain all of the hardware kits we make. All of which are made up of a combination of parts we have in our inventory.

My initial thought was to have columns such as: PartNumber, KitName, Part1Number, Part1Qty, ... PartNnumber, PartNQty etc. The numbers in the parts columns would be part numbers of items in the first table.

So since not all kits have the same number of parts I would just have to know how many parts are in the largest kit and make that many Parts columns. But then what if I have to add a new kit with more parts?

Is there a better way to do this?

Any suggestions would be greatly appreciated!

Thanks!

gnab · Accepted Answer · 2011-03-11 16:46:47Z

12

You should break up your second table into two new ones, one for kits, and one for parts belonging to kits.

Tables needed:

PARTS(PartNumber, Description, StockQty)
KITS(KitNumber, KitName)
KITSPARTS(KitNumber, PartNumber, PartQty)

This way, you add an entry to the KITSPARTS table per unique part belonging to a kit.

Sample data:

PARTS
1, "Apple", 1
2, "Orange", 2

KITS
1, "Salad"

KITSPARTS
1, 1, 1
1, 2, 1

The sample data models a single kit, "Salad", with two parts; 1 "Apple" and 1 "Orange".

edited Mar 11, 2011 at 16:46

answered Mar 11, 2011 at 16:39

gnab

9,6011 gold badge23 silver badges13 bronze badges

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

1 Comment

Matt Swanson Over a year ago

Thanks! halo and your answers really cleared things up for me.

Daniel Böhmer · Accepted Answer · 2011-03-11 16:41:04Z

3

This is a very basic question in relational DB design. The solution is easy: Invent a 3rd table containing the relationships between table A and B.

Table A products:

ID 1, name "car"
ID 2, name "bicycle"

Table B parts:

ID 1, name "wheel"
ID 2, name "seat"

Table C relationships:

ID 1, product 1, part 1, quantity 4
ID 2, product 2, part 1, quantity 2
ID 3, product 1, part 2, quantity 4
ID 4, product 2, part 2, quantity 1

I hope you can understand the example. Go on with SQL and have fun learning! I can recommend buying a good book which introduces basic SQL knowledge and you'll speed up your success with SQL.

answered Mar 11, 2011 at 16:41

Daniel Böhmer

15.6k5 gold badges40 silver badges47 bronze badges

Comments

Transact Charlie · Accepted Answer · 2011-03-11 17:13:54Z

++ For the answers above.

You should start thinking about the keys of these tables now. Each table should have something that will serve to uniquely identify a row -- done sensibly this will ensure that you can't get duplicate rows which would mess up your data.

Here's a really simple example.

CREATE TABLE Parts (
    [PartNumber] INT
    , [PartDescription] VARCHAR(50)
)

The PartNumber column would make a really good PRIMARY KEY (Assuming that no two parts thet you will ever have would have the same PartNumber) A property that uniquely defines a row and has meaning in and of itself is an example of a NATURAL KEY

CREATE TABLE Kits (
    [KitNumber] INT
    , [KitDescription] VARCHAR(50)
)

Again -- KitNumber would be a good primary key. If there wasn't a NATURAL KEY for kits then you would probably make the KitNumber a simple Auto Incrementing SURROGATE KEY.

Now to the more complicated table. The one that models the link between Parts and Kits. (Kits are made of parts).

CREATE TABLE KitParts (
    [KitNumber] INT
    , [PartNumber] INT
    )

So the only information this table contains is the links between Kits and Parts. What should the PRIMARY KEY be?

If you think about the thing that uniquely identifies a row is the COMBINATION of the two columns. Therefore the NATURAL KEY is both ([KitNumber] and [PartNumber]). This is called a COMPOSITE KEY.

Also this table should add some FOREIGN KEY Constraints. For example, [KitNumber] should never contain a value that doesn't exist in the Kits table.......

Good luck. Welcome to SQL Development.

Dave · Accepted Answer · 2011-03-11 16:45:26Z

1

As others have said you need to normalize your design.

answered Mar 11, 2011 at 16:45

Dave

4,5972 gold badges42 silver badges61 bronze badges

Collectives™ on Stack Overflow

Database design question (unknown number of columns?)

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related