#StackBounty: #database-design #erd Model multiple 1-m relationship to a single entity

Bounty: 100

I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more.

So far I can think of two ways to model this:

Approach one:

| box_id | box_content | from_user | from_office | from_import | from_storage | ...  |
|--------|-------------|-----------|-------------|-------------|--------------|------|
| box_1  | apple       | user_a    | null        | null        | null         | null |
| box_2  | orange      | null      | null        | import_b    | null         | null |

Approach 2:

BOX
| box_id | box_content |
|--------|-------------|
| box_1  | apple       |
| box_2  | orange      |

USER
| user_id | name  |
|---------|-------|
| user_a  | Rick  |
| user_b  | David |

USER <-> BOX
| user_id | box_id |
|---------|--------|
| user_a  | box_1  |

IMPORT <-> BOX
| import_id | box_id |
|-----------|--------|
| import_a  | box_2  |

But I don’t like either ways because in approach 1 there are so many null columns and in approach 2 I need to create a new relationship table every time there is a new from entity. Is there a better way to model this in a relational database?


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.