Hi There,

I am making up a small database for my work that will keep track of loads of corn that are brought in by farmers, sold to other farmers and transferred from one depot to another.

Some rules:
(1)A farmer can bring in corn he has just cut to any depot.
(2)A farmer can take out (buy) corn from any depot.
(3)A depot may transfer corn to any other depot.

These rules have been derived from the different books that we use to keep track of each load. There's a book for each rule above.

First of I was thinking that I could simply create a database table called transactions and record every transaction from every category in it. That would be cumbersome and since different details are stored for each category there would probably be a load of nulls in there, which i'm pretty sure are bad.

For example:
Rule one would require customer,depot,grain,haulier columns etc.
Rule two would require 2 depot colums but no customer column, grain,haulier etc..

Is it bad to have 2 FK's relating to 1 table in the same table?

I'm pretty sure that I should have a table for each category/rule from the list but I just need a little reassurance.

Maybe this post is a little scattered right now,sorry. I'm away from my main computer at the moment but thought i'd ask the question anyway..

Thanks in advance..

I think your getting things mixed up. You would want a table for each entity, not each rule.

example:
ASSUMPTION: When a farmer brings a crop to a depot the depot pays for it.

For tracking who owns a crop of corn you would have the first table look like this: tblOwner|ownerID (pk), ownerType (farmer/depot), description, etc. The second table would be: tblCorn|cornID(pk), ownerID(fk), price, description,etc. with a many to one relationship between the tblOwner and tblCorn based off of ownerID.

To track sales of corn you would have a sales table one ownerID would be used as the sellerID and the other the buyerID. This woull paired with the cornID, price and an other transaction information that you want.

I hope this helps point you in the right direction. I am working on a database for a berry processing plant tracking quality tests, but this is a bit different. I would have to research how TPS (transaction processing systems) work a bit more before I could say anything for sure.

How do you handle the differences between the corn brought in by a farmer (rough corn), and the corn handled by depots (dried and processed)? How do you handle shrinkage and quarantine (eg aflatoxin)?

If this is a school assignment that you want someone to do for you, then you can ignore these details but if it is for an actual working elevator operation you can't.

-PatP