Hello,
I manage a hardware database for our company and now we need to add all software and licenses to the DB.
In my Inventory table there's a PropertyNumber column representing each computer, along with a bunch of other info, e.g., IP address, location, etc.

I now need to create a Software table with rows representing each of a couple hundred types of software.
So now each property number needs to be associated with any of these 200 or so kinds of software.
What is the best way to store this information? Should I add a single new column to the Inventory table with a comma-delimited list of software IDs?

For example:

PropertyNumber Software

Computer #125 2, 5, 39, 45, 81, 93
Computer #126 3, 16, 35, 56, 25, 36, 78, 89, 104, 158

Etc. etc.

And then the Web app would parse the data in the Software column to load checkboxes on a page.

I can't think of a more practical way to do this. I certainly don't want to add 200 boolean columns to my Inventory table. Any ideas? Thanks.

John

Look at first normal form:
Fundamentals of Relational Database Design -- r937.com

For example:

CREATE TABLE Inventory
(PropertyNumber INTEGER NOT NULL,
SoftwareType INTEGER NOT NULL,
PRIMARY KEY (PropertyNumber, SoftwareType));

INSERT INTO Inventory (PropertyNumber, SoftwareType) VALUES (125,25);
INSERT INTO Inventory (PropertyNumber, SoftwareType) VALUES (125,39);
...
INSERT INTO Inventory (PropertyNumber, SoftwareType) VALUES (126,3);
INSERT INTO Inventory (PropertyNumber, SoftwareType) VALUES (126,16);

johnoa: Look up "intersection table." You have a situation with many computers, and many pieces of software. An intersection table is used to implement a many-to-many relationship.