One Table Or Three For Three Separate Objects That Have Identical Data Attributes?_问答_开发者

I am building a site for a magazine that includes three different sections- articles, blog posts, and reviews. All three of these obj开发者_Python百科ect types have the following columns in common: title, description, headline, subheadline, body, and author. Should I store them each in their own table, or should I just create one Posts table and add a category that allows me to distinguish between ['article','blog','review']?

Just to be clear- each of the three of these will be presented to the user in a completely different visual format, so it's not like it's just a blog with categories or tags of article, blog, and review. But should it be like that behind the scenes?

Having a hard time deciding if I should split them up solely based on the fact that they are in fact separate objects, or keep them all together to keep my app DRY. Will that cause a maintenance issue down the road? Performance issues? Normalization? Data Integrity? I'm not a DB guy, so any help is greatly appreciated.

I don't think it matters at all, but I'm using Rails, so I'm trying to figure out if I'm going to end up with 3 different controllers, 3 models, and 12 views, which I'm happy to do if there's a good reason behind it.

Thanks in advance!

I would recommend adding a column to indicate the type. If you call it type this will allow you to move to Single Table Inheritance (also here), even if the models begin to diverge slightly. You would then end up with...

class Post < ActiveRecord::Base
class Article < Post
class Blog < Post
class Review < Post

You can use Post in most cases, and a single Controller, but then use inheritance to handle special behaviors such as validations.

There's no reason to use separate tables if the attributes are identical between them.

Two of the objects could have identical attributes, so you would create two tables in that case.

ONe table add category field.

As long as you are SURE really SURE that this model will not change and the entities are that similiar then you will want one table with an additional field TypeID. Then when you want to show a grid with only blog posts you simply add a WHERE condition WHERE TypeID='blog posts'

Adding a foreign key to a separate type table will make it easy to add new categories as they arise. I'd recommend it over three separate objects if the behavior is not different based on type.

It's far easier to split tables later on down the road if it proves necessary, than it is to combine tables which have potentially had separate fields added to them in the meantime if you start out with them separate. I'd begin with a unified approach.

Use 3 different tables as the nature/behaviour of all three are different Do always try to stay away from redundancy but I dont think in this case its a redundancy.

Reasons for using separate tables :

1) Most probably you will add more columns to each of these tables very soon and they will be different for each of them. If you use a single table right now, you will be accomodating all of them in one table making it messy.

2) If the tables grow rapidly, you will have less problems with speed, lock, size etc.

Also, its too early to worry .. just write it through .. refactor it after a month !!

One table, also look at the question/answer to use sub-type or not; very similar.

My question to you is how are you intending to query them? Will you ever need to get information on all three types at once or not? If you need the data for all three types the single table will be simpler to design queries for. If need be you could put all the common fields in one table and add specialty tables later for information which isn't common to all three or add nullable fields for this information if it doesn't make the table too wide. If they will never be queried together, the three tables will probably be a better idea as it allows you more flexibility for changing the structure of one.

This is an example of a pattern called "generalization-specialization".

Any treatment of the object model will cover gen-spec from an object oriented point of view. Getting a good treatment from a relational point of view is a little more difficult.

If you google on "generalization specialization relational modeling" you'll get several good articles on precisely this subject. I can't say exactly what the best design is in your case, but I suspect it will be something along the lines of having one table with all the common attributes, and separate tables for each specialized item. You won't need separate id columns for the specialized tables. Just reference the common id field in each specialized table, and make this FK the PK of its own table as well.

The articles I'm pointing to at will give a more complete justification for this design. YMMV.