Ok, I am a newbie and don't really think "dimensionally" yet, I have most of my initial schema roughed out but I keep flipping back and forth on one table.
I have a Contract table and it has a quantity column (tonnes), and a net price column, which need to be summed up a bunch of different ways, and the contract has lots of foreign keys (producer, commodity, futures month etc.) and dates so it appears to be a fact table. Also the contract is never updated, if that makes a difference.
However, we create cash tickets which we use to pay out part or all of the contract and they have a contract ID on them so then the contract looks like a dimension in the cash ticket's star schema.
Is this a problem? Any ideas on the process to resolve this, because people don't seem to like the idea of joining two fact tables.
Should I put producerId and commodityId on the cash ticket? It would seem really weird not to have a contractID on it.