Search code examples
databasedatabase-designdatabase-schemasingle-table-inheritanceclass-table-inheritance

Hierarchical database model

I'm designing a classifieds website about pets that will have 3 (main) categories of ads:

A- Sale
B- Breeding
C- Jobs

The problem I'm facing is that all 3 ad categories have some things in common, but they also have some differences.

For example, categories A, B and C all have the following in common: 

category_id
ad_id
user_id
location

However, category B ads cannot be classified as "Looking For/For Sale", whereas B & C can. Furthermore, each category will have its own subcategories. For example, category A would have an adoption subcategory and a sale subcategory & category C would have a Jobs subcategory and a Services subcategory.

They'll also have some uncommon attributes like salary in category C only.

I've began working on it as an entity-attribute-value model, but I have several concerns:

  1. Is this going to affect performance? Because users should be able to search based on some of the unique attributes. So wouldn't querying through one huge table affect performance?

  2. One of my attributes is "Animal" (unique to A & B), initially I wanted to have an Animal table and a Breed table (Similar to how Location is in a table of its own for classifieds DB models). But I can't imagine how I can do this with the entity-attribute-model.

Here's the database design I've come up with: https://i.sstatic.net/x8zOS.png

[Update]

After reading up in class table inheritance, it seemed like a better fit than the EAV model, so I changed my EER to this: https://i.sstatic.net/RvzWP.png

  1. Job Ads and Sale Ads can be classified as "Looking For/Offering", but there wouldn't be many differences in the attributes, probably just their names (Price vs. Preferred Price for example). So do you think it would be necessary to add another level of inheritance here, or would it be a hassle?

  2. A Sale Ad can be a pet for sale, pet for adoption, requesting an adoption, requesting buying a pet. Do you think I should split these into two table. Adoption and Sale? There wouldn't be a big difference in the attributes, probably just one different attribute.

Solution

  • Your question sounds like a case of class/subclass. In object modeling, the design uses the fundamental mechanism of inheritance, and the design pattern of subclasses that extend and revise superclasses.

    Relational modeling is different from object modeling, and table design is different from object design. There are a couple of techniques that help bridge the gap. There are two tags in this area that are relevant, and . I'm about to take the liberty of adding those two tags to your question. Back them out if you don't like them.