Database Design

[MasterChief]

I am trying to make a document/training website. What I would like is
for somebody to click on a job on the left, have it display all the
programs/software that are available for that job and then once the
program/software is selected it will display all the documents for that
particular job/program. The catch is that there can be a document that
is associated with many different jobs as shown below. As you can see
Transaction Inquiry is listed in both jobs. Right now the way I have it
setup I can display just fine but my real problem is I don't know how I
will edit this database using ASP.NET and don't know if I set it up
right. Please tell me if there is a much better way of setting up the
database. I have one table called Job with Job_id, Job_name. Then
another table called Program with program_id, program_name. Another
table called Document with doc_id, doc_name. The way I am linking it
together is a seperate table called join that has job_id, program_id
and doc_id where those id's link to the seperate individual tables. Is
this the correct method?

Job: Outside Sales
Program List:
POS
- Transaction Inquiry
- Sales Inquiry
OUTLOOK
- Calendar
- Send and Recieve
INTERNET USAGE

Job: Inside Sales
Program List:
POS
- Transaction Inquiry
- Price Matrix
OUTLOOK
- Calendar
- Send and Recieve

 

[Earl]

Think of the database design separate from the program design: how do the
entities interact? Are the relationships 1-many or many-to-many? It sounds
like a Job can have many Programs and that Documents can have many Jobs. If
both of these are many-to-many, then you should have the two relationship
tables as shown here:

Jobs
JobID (PK)
JobName

Programs
ProgramID (PK)
ProgramName

JobsPrograms
ProgramID
JobID

Documents
DocumentID (PK)
DocumentName

JobsDocuments
JobID
DocumentID