What I have are four tables; report, participation, person and vehicle. The participation table is a join between the other three. The cardinality between the report table and the person and vehicle tables is 0:M. These two tables have optional participation.

I've created the tables and dumped sample data in them and they seem to give me exactly what I am looking for. The only caveat is that I have had to enter a single row of "N/A" in both the person and vehicle tables to satisfy the FK constraint in the "participation" table. It doesn't seem optimal but I'm not sure how else to handle this. Could someone please review the pic I have included and advise?

Thanks,
Frank

Seems your problem involves enforcing a unique constraint across nullable columns.
What is your database platform? In SQLSVR, for instance, you can enforce this constraint using an indexed view, rather than the participation table's primary key.

Hi Blindman,

Thanks for the hand. I'm using mysql for this.

Unless I'm not following you, and I'm almost certain that's the case, what I am doing here is selecting a "dummy" row of data which is coming out of the party and/or vehicle table to satisfy the FK constraint(s) in the participation table. The participation table PK is made up of the FKs from the report, party and vehicle tables. I'm not sure I want to enforce the constraint as you say, but rather not enforce it. Am I misunderstanding?

Here is the DDL. It may help.

Always willing to help a fellow noodly one.
But I don't understand what you mean when you say you don't want to enforce that constraint. If this is the case, then don't set those three columns as the primary key.
Should a given combination of Report/Vehicle/Person be allowed more than once?
Should a report be allowed to reference a vehicle more than once, if each reference specifies a different person?

I'm not sure I'm following either, but do you want to do something like this:
Implementing Table Interfaces - SQLTeam.com
(ignore the "this is kind of like object oriented principles in databases" overtones)?

pootle flump ur codings are working excelent.

The idea behind the participation table is to associate the report to either one or both of the other entities. (when they participate)

If we take the constraint between party and participation table for example...

If I have a vehicle to enter into the vehicle table, I want that vehicle associated with the report. Let's say that there is no party to go along with the report. If you look at my PKs in the participation table (which really is nothing more than a join table) I have the PK from the party table in there as well, so when I enter data for only the vehicle, the PK from the party table is expecting a value as well, so when I say that I don't want to enforce a constraint, that's what I mean.

Where I am running into problems is when there is data to enter for either the party OR the vehicle. These are both optional constraints because they will not always participate.

If I remove the PKs from the party and vehicle relationships and leave only the report PK, then I would only be able to enter a single report, vehicle and party and that isn't correct because the relationship is 0:M.

*Lightbulb goes on* I could however remove the PKs from the vehicle and party relationships as you suggest and add an identity column. I could then make the party and vehicle relationships nullable and still be able to enter more than one party and vehicle. Am I correct?

I hope I'm a little clearer, it's been a long night.

Heya Poots. Thanks for the link. That's exactly what I want to do however, mysql won't do check constraints.

I know I tend to over explain things sometimes but all I am trying to do is satisfy one or both of the FK constraints in the participation table. The vehicle and party tables can optionally participate in the relationship. The way I have it now, I am forced to enter a value for both tables.

Well, I think Blindman was right on the money. Here's what I did.

I left the PK on report_seq, removed the PKs from vin and state_id_no. I made both of these columns nullable. I added an identity column and a normal index and it works perfectly.

At least I no longer have to enter dummy data to get my data in. Does this sound ok?

In your original schema report_seq was not unique. Now you are saying it is. But if it is unique then I don't understand what you think you are gaining by adding an IDENTITY column.

What I would suggest is that you create separate tables for what are apparently different subclasses of "partcicipation": One with Party, one with Vehicle and one with both. That way you can enforce whatever uniqueness constraints you require. I'm still not clear exactly what all the keys are but by creating the extra table(s) you ensure that they can all be enforced.

i agree with dportas, the issue of uniqueness of the chosen PK(s) is very much in question

tip: when you think that applying a surrogate key (e.g. auto_increment) solves your modelling problem (by modelling, i mean the selection of PKs and FKs, nullability, uniqueness, and so on), you can be almost certain that what you are doing is simply masking the underlying unresolved issues

please show your new DDL after the changes you spoke of

report_seq is a unique number within the report table. In the participation table, it's used it as part of a compound PK.

dportas, could you please show me what you mean by this? I'm sorry but I don't understand.

The best way to define the problem clearly and avoid any confusion is to post your CREATE TABLE statements (including all the keys please). I think that's what we need in order to help you further.

Wow. I thought I had a kwel design going on. Thanks for the tip Rudy, those are words to live by.

Here's the DDL after I made the changes.
I restructured the tables as dportas suggested. In the new pic you can see that each child table is dependant upon the report table. This schema doesn't work for me though. Please tell me if this is what everyone thinks I should do, then I'll explain why it won't work.

EDIT: I posted the DDL from the other two tables as well. Just a side note: I didn't alter these tables in any way. All of my changes have been to the participation table.

okay, here's where i am in catching up to you

there is a report table, which has report_seq as it primary key

so report_seq identifies (there's that word again, eh) a unique report

now we have a "participation" table, which i don't fully understand yet, but which has report_seq as a foreign key

the only possibilities, as seen from the point of view of the participation table, then, are that each report has one participation row, or each report has multiple participation rows

with me so far?

and now it seems to me that whatever it is that might require multiple participation rows for a single report, would involve multiple parties, or multiple vehicles, or multiple combinations of parties and people

have i got that right so far?

if this is correct, could you please give an example of a report with multiples of one or the other or both, and what this "participation" then actually means

thanks