Link one table to another?

Question

i'm trying to set up some tables in my SQL Express server, and i want to do something to the effect of this:

create table programs (
    progid int primary key identity(1,1),
    name nvarchar(255),
    description nvarchar(500),
    iconFile nvarchar(255),
    installScript nvarchar(255)
)

however i want each "program" to have a set of categories associated with it, what's the best way to do this? i read somewhere that it's not a good idea to store multiple items under a single category entry.

Answer 1

First, define a table (assuming you haven't already) to contain the list of categories with id and value columns at a minimum.

Can a program be associated to only one category? If yes, add a column to the PROGRAMS table and create a foreign key reference between the category column in PROGRAMS to the id column in the CATEGORIES table.

If you want to be able to associate one or more categories to a program, you'll need an additional table:

PROGRAM_CATEGORIES_XREF:

• PROG_ID (pk, fk)
• CATEGORY_ID (pk, fk)

In this case, you won't need to add a column to the PROGRAMS table because of the link between the PROG_ID columns.

Answer 2

Create another table for categories like this:

create table categories(
   catid int not null primary key identity,
   caterogy varchar(255) not null
)

then store categories related to program using third table:

create table progcategory(
    progid int foreign key references programs(progid) not null,
    catid int foreign key references categories(catid) not null,
    primary key(progid, catid)
)