1

I'm designing a website with courses and jobs.

I have a jobs table and courses table, and each job or course is offered by a 'body', which is either an institution(offering courses) or a company(offering jobs). I am deciding between these two options:

option1: use a 'Bodies' table, with a body_type column for both insitutions and companies.

option2: use separate 'institution' and 'company' tables.

My main problem is that there is also a post table where all adverts for courses and jobs are displayed from. Therefore if I go with the first option, I would just need to put a body_id as a record for each post, whereas if I choose the second option, I would need to have an extra join somewhere when displaying posts.

Which option is best? or is there an alternative design?

gray
  • 798
  • 18
  • 31

2 Answers2

2

Don't think so much in terms of SQL syntax and "extra joins", think more in terms of models, entities, attributes, and relations.

At the highest level, your model's central entity is a Post. What are the attributes of a post?

  • Who posted it
  • When it was posted
  • Its contents
  • Some additional metadata for search purposes
  • (Others?)

Each of these attributes is either unique to that post and therefore should be in the post table directly, or is not and should be in a table which is related; one obvious example is "who posted it" - this should simply be a PostedBy field with an ID which relates another table for poster/body entities. (NB: Your poster entity does not necessarily have to be your body entity ...)

Your poster/body entity has its own attributes that are either unique to each poster/body, or again, should be in some normalized entity of their own.

Are job posts and course posts substantially different? Perhaps you should consider CoursePosts and JobPosts subset tables with job- and course-specific data, and then join these to your Posts table.

The key thing is to get your model in such a state that all of the entity attributes and relationships make sense where they are. Correctly modeling your actual entities will prevent both performance and logic issues down the line.

For your specific question, if your bodies are generally identical in terms of attributes (name, contact info, etc) then you want to put them in the same table. If they are substantially different, then they should probably be in different tables. And if they are substantially different, and your jobs and courses are substantially different, then definitely consider creating two entirely different data models for JobPosts versus CoursePosts and then simply linking them in some superset table of Posts. But as you can tell, from an object-oriented perspective, if your Posts have nothing in common but perhaps a unique key identifier and some administrative metadata, you might even ask why you're mixing these two entities in your application.

Kyle Hale
  • 7,912
  • 1
  • 37
  • 58
  • This is extremely helpful, you've made it clear that the post table is my central entity.. I'd only half realized that...I have built on to a CMS system and adapted it to courses and jobs which is why posts is central. Thanks, a brilliant answer – gray Oct 17 '13 at 15:35
2

When resolving hierarchies there are usually 3 options:

  • Kill children: Your option 1
  • Kill parent: Your option 2
  • Keep both

I get the issue you're talking about when you kill the parent. Basically, you don't know to what table you have to create a foreign key. So unless you also create a post hierarchy where you have a post related to institution and a separate post table relating to company (horrible solution!) that is a no go. You could also solve this outside the design itself adding metadata in each post stating which table they should join against (not a good option either as your schema will not be self documentation and the data will determine how to join tables... which is error prone).

So I would discard killing the parent. Killing the children works good if you don't have too many different fields between the different tables. Also you should bear in mind that that approach is not good to solve issues wether the children can be both: institution and companies but it doesn't seem to be the case. Killing the children is also the most efficient one.

The third option that you haven't evaluated is the keeping both approach. This way you keep a dummy table containing the shared values between the bodies and each of the bodies have a FK to this "abstract" table (if you know what I mean). This is usually the least efficient way but most likely the most flexible. This way you can easily handle bodies that are of both types, and also that are only of type "body" but not a company nor an institution themselves (if that is even possible or might be possible in the future). You should note that in order to join a post to an institution you should always reference the parent table and then join the parent with the children.

This question might also be useful for you:

Community
  • 1
  • 1
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123