The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

do we need foreign keys?

We are setting up a database for users to upload procedures. There are several departments which contain several manuals which then contain several subsets, then categories, then the actual procedures. whew

So far, we have 5 tables to handle this. We thought we should set-up foreign keys to link the tables but are getting errors. We can create the first foreign key but it won't let us create any more foreign keys using that field.

Are we going about this the wrong way? We will need to match records later and want to be able to have drop-down menus generated from the database for depts, manuals, subsets, & categories. We have done something similar in the past but had only 2 tables to join. If anyone has any tips/suggestions please let us know.

Each manual can only belong to one department - but each department can have several manuals - there are several categories in each manual which can have several policies

Subsets can only belong to one manual, but can have several categories.

We need to generate drop down menus for dept, manual, subset and category names so that when the user uploads the actual policy files they can choose which dept, manual, subset and category each policy belongs to.

Thanks so much for taking a look at this! We want to make sure we are on the right track before we start programming the forms.

well, i cannot comment on the design without seeing a lot of data to understand the subtle relationship differences between manual departments, manual policies, subset policies, and whatever (no, please don't post a lot of data )

as far as the foreign keys are concerned, make sure you have an index on each of them in the table before declaring the foreign key constraint

Are you just using the KEY syntax as shown in your original post to denote a foreign key giving you no referential integrity or did you update them to use the FOREIGN KEY (some_id) REFERENCES another_table(id) ON UPDATE CASCADE ... bla bla bla for referential integrity?