Aspect of DB architecture

Question

For example, I have 3 types of some entity, let's name it as Offer. So, can be AcceptOffer, DeclineOffer and OutstandingOffer.

AcceptOffer:

- UserID
- ContactID
- Notes
- FirstContactDate
- LastContactDate
[... and 5-10 the unique fields...]

DeclineOffer:

- UserID
- ContactID
- Notes
[... and 5-10 the unique fields...]

OutstandingOffer:

- UserID
- ContactID
- FirstContactDate
- LastContactDate
[... and 5-10 the unique fields...]

Also, imagine, that we have a table table named Vacancy, which has a field OfferID and relationship to Offers

Vacancy table:

- ID
- VacancyName
- OfferID

So, these entities have some common fields (in all 3 entities), some common fields only between 2 entities and each entity has suite of the unique fields.

Question - what is the best way to create a relational DB architecture? I see 2 approaches:

1. One common table Offers.

pros:

a) easy to develop

b) clear and understand relationship to Vacancy table

cons:

a) some fields will be marked as nullable even when they are not nullable for used cases. I.e. imagine that FirstContactDate and LastContactDate are not nullable for AcceptOffers and OutstandingOffers. But we have to mark it as nullable to allow storing DeclineOffers. So, we have to write rules to check AcceptOffer (and DeclineOffers) has not nullable FirstContactDate and LastContactDate.

b) many fields are unique only for one type of entity, but exist for all types

2. Three Tables (one table per type of entity)

pros:

a) more clear and "adequate" architecture

cons:

a) More complex architecture to build relationships between Vacancy and each of these 3 tables. I even can't imagine how to do it without one more table and additional rules for this table

Which approach is more appropriate and why?

Answer 1

That depends on how you estimate the degree in which your models will diverge with passing time.

If you expect them to be too different, I would say - create separate tables. If you expect them to share much - go for one table.

There is no rule of the thumb for such cases :)

Also that depends on what ORM you use. It may happen, that it doesn't support table inheritance and you will be forced to create three totally different models.

Also, performance must be taken into account. When you have too many fields with indexes on them, that slows down all operations, except selects. So, you may want to create functional indexes on such fields to avoid bottlenecks.