Using a single wiki table for multiple subject tables

Question

Hello, I wonder to know if someone can help to understand if I'm doing well using a single table with wiki content for multiple subjects, so:

TABLE wiki
 ==>  wiki_id
      wiki_content
      wiki_language
      wiki_user_id

TABLE houses
      house_id
      house_name
      house_year
      house_location
 ==>  wiki_id

TABLE architects
      arch_id
      arch_name
      arch_born
      arch_died
 ==>  wiki_id

I think it could be a right way if i use:

SELECT * FROM houses WHERE house_id = '123' INNER JOIN wiki ON houses.wiki_id = wiki.wiki_id

but I don't have experience on databases

the wiki structure, is substantially identical in the both cases, so, am I wrong using one wiki table for two different tables? Could I encounter problems in the future on this db design solution?

Answer 1

If the wiki information is related directly to houses and architects then I see no problem linking them with foreign keys. It is just a one to many relationship for multiple entities. As long as those are related then it is fine to link them.

This might only be a data integrity issue if the set of wikis that are related to houses is completely separate from the wikis that are related to architects since your foreign key constraint will not ensure that is always true.

Answer 2

If you handle houses and architects within the same wiki, I'd think that would be how you'd want to do it.

The way you have it, you can see which houses (and which architects) are associated with a given wiki. Separating things into two wiki tables would indicate that they're not really part of the same wiki, which might indicate that your wiki is too "tightly coupled" to your houses and your architects.

Answer 3

SELECT *
FROM wiki AS w
    JOIN houses AS h ON w.wiki_id = h.wiki_id
    JOIN architects AS a ON  w.wiki_id = a.wiki_id
WHERE house_name = 'big tall house'
    AND house_location = 'two blocks that way'
;