CODEWITHSUNDEEP
sqldatabase-design
Matt Swanson
Matt Swanson

Reputation: 1194

Database design question (unknown number of columns?)

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!

Upvotes: 0

Views: 1513

Answers (4)

Transact Charlie
Transact Charlie

Reputation: 2203

++ 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.

Upvotes: 2

gnab
gnab

Reputation: 9561

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".

Upvotes: 12

Dave
Dave

Reputation: 4597

As others have said you need to normalize your design.

Upvotes: 1

Daniel Böhmer
Daniel Böhmer

Reputation: 15381

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.

Upvotes: 3

Related Questions