help with primary, foreign keys

Hello,


I'm working on a database schema for an application I'm writing and was wondering if someone could give me some advice and/or pointers. I've attached a .gif file that diagrams the schema using Microsoft Access's "Relationships" page, but the text below might help explain exactly what's going on, too.

Essentially what this application does is store name/value pairs for various URLs in order to be able to replicate the results on a given page. There is a company table, which is joined to a "service" table (each company has multiple services, which basically correspond to a basic URL). In turn this is joined to a parameters talbe since each service has many different parameters that it can accept.

Furthermore, each company has users, so that's another join, from the company table to the person table. The person table is then joined to another service table - essentially, each person can have different iterations of each of the entries in the first service table, and are stored here. Finally, another table stores the parameters for each saved service of each user, and is joined to both the "person_service" table and the "company_service_param" tables.

Take a look at the attached file - it'll make more sense that way.

Anyways, I defined all the relationships in a way that I think is good, and Access didn't throw any errors when I set them up. I chose to enforce referential integrity and cascade deletes and updates, but when I try to change a company_id in the company table, it gives me an error and appears to not to want to cascade through (specifically in company_service_param).

Does anyone have any advice? Like I said, the relationships seem to make sense but for some reason Access is crapping on me... thanks everyone!

okay... i have had a look at the diagram and been trying to figure it out myself.

I have a question for you now regarding your design:

Is there a need for user id to be repeated in the other 2 tables? Looking at the setup the only reason would be to make the SQL "easier" but its called redundant data.

Due to the amount of relationships present in the database you might have put yourself into the following position:

(i hope you understand the sentence below, its hard to explain)

You cannot enter data because there will always be somewhere where it doesnt exisit yet since you haven't had a chance to create it. But you are unable to create the data it needs because of that first part hasn't been done. In short with too many checks in place you cant add data since it cannot be added to 3 tables at once.

i would try and redesign the setup so that there is only 1 link between tables. If information like the user id is not really needed in the other table and can be found via a query/table join later than that would be a better way.

I hope the above has made sense.... its can be hard to explain what i mean.

Ya, what Andreas said .

You've got a lot of redundant keys, actually.

Your person_service, company_service_param, and person_service_param tables don't need the company_id.

Your person_service_param table doesn't need the person_service_number or company_service_param_name fields.

Your foreign-key relationships make those fields redundant. I don't know if removing those will fix your problem, but I wouldn't be surprised.

hey guys,


Thanks a lot for your input. I was always a fan of "make sure every table has a single field as primary key" but this time I was trying something new (based on someone else's data structure). I'll give some of your suggestions a whirl... thanks so much.


-Cliff