Menu
Legend:
How to model a diamond like (if term is correct) relationship? Better to explain using a simplified example:
There is organization, item and tag entities.
My aim is to model:
tag is unique by itself and belongs to a single organization.item is unique by itself and belongs to a single organization.tag/item pairs must belong to same organization. (i.e. item from organization A cannot pair with a tag from organization B)I diagrammed two alternative solutions, but none of them satisfied me.
Diagram 1 breaks 3rd aim: items and tags are unique by themselves using id as primary key, but there is nothing to stop insert pairs into item_tag which belong to different organization.
Diagram 2 does not break, but bends 1st and 2nd aims: organization_id is added as a Primary and Foreign Key to item and tag tables and item_tag.organization_id column references both. This prevents pairs from different organization. tag.id and item.id columns are part of a unnecessary composite primary key now, because in reality single id column represents uniqueness of the item and tag.
How can I model those requirements correctly?
394
Rectangles represent Entity sets, diamonds represent Relationship sets and ovals represent Attributes of entities or relationships.
To create an ERD, select Diagram > New from the toolbar. In the New Diagram window, select Entity Relationship Diagram and click Next. Enter Bus Route Management as diagram name and click OK. Let's start by creating the first entity Route.
— Partial Key A weak entity has what's called a “partial key”. It's one or more attributes that uniquely identify a weak entity for a given owner entity. In our example, the dependent name is unique for every employee. It's sketched the same as a normal attribute, but, with a dashed underline.
To enforce referential integrity, you'll have to ...
organization_id in all tablesUNIQUE (or PK) constraints on (organization_id, id) in both tables tag and item
item_tag matching the columns of those UNIQUE constraints.If you don't include the organization_id (logically redundantly) there would be nothing to keep you from linking items and tags from different organizations (by mistake).
That would be your diagram 2. But do you really need data type uuid for tags? bigint or even int should suffice, while being a bit smaller and faster.
Closely related case with code example for PostgreSQL:
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
