How do I model a 1:N relationship that is constrained a by third entity?
My predicament: I am Chen-mo开发者_如何学JAVAdelling an accounting database in which the client to account entity relationship is one-to-many (1:N), but the client to office_location relationship is unique (one-to-one, 1:1). I've tried figuring a ternary relationship and also resolving it into a weak entity attached to the other three through binary relationships. I end up with something like this:
OFFICE_LOC ---SERVES(M:N)=== CLIENT
OFFICE_LOC ---ANCHORS(1:N)=== ACCOUNT (WE)
CLIENT ===OWNS(1:N)=== ACCOUNT (WE)
I am having a tough time figuring out how to model the condition that a client can have only one account per office_loc even though they can have many accounts, generally speaking.
I don't know if your asking specifically how to model something in Chen modelling (which I know NOTHING about) or if you're asking how to data model this relationship. If the former ignore this answer, if the latter...
Client <---Client_2_Account---> Account
The Client_2_Account many-to-many table will have an Office_Location fkey field in addition to the fkey fields for client and account. Put a unique constraint on the compound key of client/office_location and a unique key on account. Then a client can have only one account record for an office location and each account record can be owned by only one client.
精彩评论