Some, but not all records in one table are related to records in another ...

[J P]

Hi,

I am somewhat new to database's and have run across a situation that I
don't quite know what to do with. I suspect I am not viewing my
dataspace correctly but if that is the case I can't seem to figure out
what I am doing wrong.

The application is a small inventory tracking database. I am having
trouble with the relationship between two of the tables. One table is
an inventory transaction table which has one record for each change in
the inventory level of a particular product. A separate table tracks
sales orders. Since each individual order could involve multiple
products I set up a 1 to many relationship between the orders table
and the transaction table.

The problem is that not every transaction involves an actual customer
order. For instance some might be taken to as samples for potential
customers, others might involve a product return, etc...

I think this is a problem because I am concerned that at some point it
is going to be a problem that not every record in the transaction
table will be related to an order. For instance if I understand what
I am doing this type of situation would prevent me from using the
"referential integrity" forcing features of access which, at least to
my newbie mind, seemed like a good thing to always enforce.

Has anyone more experienced every encountered a problem similar to
this and if so how did you model your dataspace in that instance ?


Thanks,

Jay

 

[Allen Browne]

So your Transaction table has an OrderID field that sometimes has a value,
and sometimes is left blank?

That's fine. You can still use Referential Integrity. RI does not mean you
have to have a value in the field; it means that if you have a value, it
must be a valid one. Leaving the foreign key null is quite okay.

You may need to use outer joins. If that's a new concept, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[J P]

So your Transaction table has an OrderID field that sometimes has a value,
and sometimes is left blank?

Yes. That is a much more succinct way to say what I was trying to
describe.

That's fine. You can still use Referential Integrity. RI does not mean you
have to have a value in the field; it means that if you have a value, it
must be a valid one. Leaving the foreign key null is quite okay.

Ok. Thanks. I did not know that.

You may need to use outer joins. If that's a new concept, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Thanks again for your help. I will take a look at that page and the
other articles on your site.