Is this a step in the right direction for normalization? SQL ‘inheritance’ and Many-to-Many relationships

Posted on

Question :

Context:

So, this is kind of a compound question, but they go together, and definitely wouldn’t make sense split up into separate questions.

I’ve been working on a new database for what will end up being a programmer’s blog/portfolio, with the occasional tutorial or demonstration, written in ASP.NET MVC 5 and Entity Framework 6. About halfway into designing the database schema, I noticed almost all of the tables contained the same 5 basic columns: ID, Title, Description, DatePosted, and URLStub. While not necessarily redundant, as most of the data would be unique, except maybe where an article is linked to a Demo, it looks wrong or sloppy.

Models:

This is the model I initially set out making:
Diagram of my initial model

I swear I auto-arranged/auto-sized those tables


And this is what I ended up shifting towards, but I still feel like I’m doing something stupid:
Diagram of my new model

Questions:

  1. Is the new model even plausible with entity framework?
  2. Is it an improvement (more normal) over the first one?
  3. Should I have even worried about the first one?
  4. Can anyone see anything inherently stupid with the new model?

Thank you in advance for any input, and also sorry in advance if this is going to “create too much discussion” or is “primarily based on opinion”.. I don’t really know a better place to ask this, and get knowledgeable answers.

Answer :

This looks like the party relationship model (see https://stackoverflow.com/questions/4969133/database-design-articles-blog-posts-photos-stories).

I recently went through something similar, combining different content types under a single table with subtables for each unique content type.

I’m not sure about the entity framework, but in the new model you have, you’ll have an easier way to link content to other content by having a central place storing the ids/primary keys. It’s a common design pattern, so I don’t think there’s anything “inherently stupid” about it.

I don’t think you needed to worry about the first one, but the second one is obviously better thought out.

Leave a Reply

Your email address will not be published. Required fields are marked *