>If you performed a
SELECT DISTINCT task_name (or whatever) FROM ContTask
, how many records would be returned?
Not alot. There could be up to 5 Spaces and 12 tasks per contract.
>I'd drop the fk_Contract field from each of these tables.
>Then the ContFee table becomes a many-many-many table relating Contract-Task-Space and adding necessary information about that m-m-m relationship.
Gotcha. ContFee is a child of Task/Space, and they know who the parent contract is.
>You don't say what other info in ContTask and ContSpace relates to that specific Contract-Task and Contract-Space relationship,
None. That is the full structure for ContFee, ContTask, and ContSpace.
>but if each of these has a fee anyway, it might make sense to move those fields to the ContFee table as well, so then the table could be described as "Attributes for each specific Contract-Task-Space combination"
That was my first idea. I just becomes difficult to keep that table in-sync. A business rule states; "Each task must have a fee for each space." So I have to code to make sure that happens. Also I just found out that there is a 3rd table "ConsultantService" that has to have a fee for each space. And another parent table "WorkOrder" has to have mirror image of those tables and wo Fee table has an additional field "SquareFeet".
I keep running into the need to get just the Spaces or just the Fees for a specific contract, so I thought a ContSpace table would make it easier.
>If you don't want to or can't change the structures of these, I'd suggest dropping fk_Contract from ContFee, since it relates to both ContTask and ContSpace and those in turn relate to Contract. Otherwise, it could get a little squirrely making sure that the Contract key actually belongs with this particular ContTask key and this particular ContSpace key.
No problem, I can drop the fk_Contract field.
Thanks.
>HTH (and that I didn't ramble too much)
Roi
'MCP' Visual FoxPro
In Rome, there was a poem.
About a dog, who found two bone.
He lick the one, he lick the other.
He went pyscho, he drop dead!