Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Comma delimited list as a column?

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by johnoa View Post
    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.
    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);

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •