4

I need help with an SQL decision that has confused me for a while.

I'm trying to make a short story website where users can write their own stories and can browse each other's, etc. I've also got a collection of classic short stories written by great writers from the past. I'm confused as to whether I should store both types of story in the same database table.

I want to keep the two types of stories (classic authors/users) distinct to some degree, since you should be able to search the website and filter out user stories from the results. But I can't just have a single database row in the table to represent this, ie a boolean CLASSIC, since with classic short stores, several other of the rows would be different too - there is no user, the date would be YYYY (ie, 1869) instead of a full datetime when the user submitted it.

Yet I can't quite justify putting them in separate tables either. When most of the attributes are the same, should I really have two different database tables for short stories? At the moment I am filling in NULL into the user row for classic short stories, and my filtered search has an option to search only through classics, which selects from the database where user is NULL. This seems to hit performance though, when you're searching through a huge database of potentially millions of user stories just to find a few thousand classic stories.

Note that there are other tables too, like tags for the stories, linked to the short stories table.

So I'm basically asking you SQL experts - is there enough justification for separating the two types of information into different tables? I'm currently using SQLite in development but will switch to MySQL or PostgreSQL later.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Laurence
  • 661
  • 7
  • 24
  • 1
    In short, you have a hierarchy and you need to see how to turn it into the physical model. Probably [this question](http://stackoverflow.com/questions/8685621/what-is-the-best-database-schema-to-support-values-that-are-only-appropriate-to/9460541#9460541) might help or maybe [this simplified one](http://stackoverflow.com/questions/19430204/database-design-structure/19430750#19430750). – Mosty Mostacho Oct 21 '13 at 20:01

2 Answers2

5

I'd probably go with a "parent-child" table structure, where you have matching primary keys across tables, something like:

Stories: StoryId (PK), StoryType (U or C), StoryText, etc. (all of the shared stuff)
UserStories: StoryId (PK and FK), UserId, etc.
ClassicStories: StoryId (PK and FK), AuthorName, etc.

Then if you want, you can build two views around them:

V_UserStories: StoryId, StoryText, UserId, etc.
V_ClassicStories: StoryId, StoryText, AuthorName, etc.

With this setup, you're not wasting any columns, you're keeping shared stuff together, while still keeping the two types of stories easily logically separate if you need them.

Joe Enos
  • 39,478
  • 11
  • 80
  • 136
  • 1
    The `StoryType` column isn't technically needed in this scenario - if you inner join against UserStories and get a record, then it's a user story, and if you inner join against ClassicStories and get a record, then it's a classic story. The StoryType column would just be there for convenience. – Joe Enos Oct 21 '13 at 20:01
  • +1! So good to hear people recommend such design methods nowadays! Plus you can relate your `tags` table with `Stories`, for common functionality, while making your `sharing` table point only to `UserStories` for apparent reasons. I could add a lot more, but I already wrote an [article about IS-A relations](http://www.geomagas.gr/index.php/is-a-relations-in-relational-databases/) a while ago. – geomagas Oct 21 '13 at 20:10
  • 1
    Thanks everyone, I'll choose this design. Mosty Mostacho seems to be an expert at answering these types of questions and all these answers have made the whole issue clearer for me. One table for common functions is best. – Laurence Oct 21 '13 at 20:16
0

To make such a decision you have to think if the field you want to insert into your table for your table only and nothing else. for example

Story and type of story, if a story can have several types of stories and / or a type for several stories then yes you must make a specific table kind of history, but if only one type of story concern one story then you insert the type informations (name, description etc ...) directly into the stories table.

Victor
  • 116
  • 1
  • 5