3

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.

  • 1
    The only thing that missing is a subtype identifier in the base table (ContentBase) and the subtype tables (Articles, Demos, etc.) to ensure that you don't get by mistake a ContentBase row added in 2 subtables (as both an Article and a Demo for example.) Adding such a column though will lead to slightly more complicated design (composite FKs instead of single-column ones between the base table and the subtypes) and I have no idea if EF can handle that. If not, you have to enforce that constraint in the application level. – ypercubeᵀᴹ May 30 '14 at 8:35
  • Okay, that makes sense, but what if I want to be able to write an article, mark it as a tutorial, and then be able to add a corresponding demo without repeating the basic information that would more than likely be identical? Or lets say i make a new project, and want to be able to write a corresponding article to introduce the newly uploaded project? – user2759920 May 30 '14 at 11:42
2

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.

  • 1
    Thanks, I wasn't aware this was a common design pattern, though I was fairly certain someone had to have thought of something similar at some point. I just wanted to make sure I wasn't going to make a huge headache for myself, trying to work with a fundamentally flawed structure. I'd up-vote your answer, but my opinion doesn't matter enough yet... – user2759920 May 30 '14 at 3:47
  • 1
    Both the question here and the linked one look more like the supertype/subtype pattern. – ypercubeᵀᴹ May 30 '14 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.