CODEWITHSUNDEEP
mysqldatabasedata-modeling
Anthony
Anthony

Reputation: 5433

How to handle tables with multiple parents

I'm having trouble modeling a particular database structure I'm working on. To be short, considering the following:

I can't quite figure out how to model this at the DB level. The first three are easy:

webpage
----------
id
name

thread
---------
id
page_id
name

comment
--------
id
thread_id
name

But if I wanted a single table of complaints, how would one model that? I don't think you would want to do:

complaint
----------
id
page_id
thread_id
comment_id

If you ever added a new object type, like picture, you'd have to add more columns to the complaint. Is there a better way to do this, or is at as good as it gets?

Thanks in advance, - Anthony

Upvotes: 1

Views: 294

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Another approach is to have a new entity table that has a supertype/subtype relationship with the 3 tables (webpage, thread, comment):

entity
----------
id (PK)

webpage
----------
id (PK)
name
FOREIGN KEY id REFERENCES entity(id)

thread
---------
id (PK)
page_id
name
FOREIGN KEY id REFERENCES entity(id)

comment
--------
id (PK)
thread_id
name
FOREIGN KEY id REFERENCES entity(id)

complaint
----------
id (PK)
entity_id 
FOREIGN KEY entity_id REFERENCES entity(id)

This way, creating of a new webpage (or thread or comment) or deleting one will be slightly more complicated (inserting or deleting a new row in two tables than one.)

Upvotes: 0

Antony Scott
Antony Scott

Reputation: 21998

I would create the complaint as an entity in it's own right, then have link table between all the different things it can be associated with.

So, I'd have the following tables ...

  • complaint
  • compliant_comment_link
  • complaint_thread_link
  • complaint_page_link

This is a slightly different variation on Waleed's solution. As with all things like this, there are many ways to solve it :)

The advantage of this approach is that you can have foreign keys to maintain data integrity. The disadvantage is that whenever you need to have complaint against a new "thing" you will need a new link table, but I suppose you'd have to create a new "thing" table anyway.

Upvotes: 1

Waleed Al-Balooshi
Waleed Al-Balooshi

Reputation: 6406

One solution off the top of my head is to have a table:

ObjectType

-------------------
|  id  |   name   |
-------------------
|   1  | Webpage  |
|   2  | Thread   |
|   3  | Comment  |
-------------------

Then your complaint table can be as follows:

----------------------------------------
|  id  |   object_type_id   | objectid |
----------------------------------------
|   1  |         1          |    1     |
|   2  |         1          |    2     |
|   3  |         2          |    1     |
---------------------------------------|

Of course this could add additional work later on when querying the complaint table and joining with the others, but that all depends on what you want to query.

Upvotes: 0

Related Questions