Scaffolding a Generic Admin - Part 2 - The Data Model

Posted by Paul Marcotte | Tags: Database , Modeling

Before we can scaffold screens for our generic admin, we'll need a data model. Below is a E/R diagram for the trivia game data model, which contains the following entities: User, Theme, Category, Game, Question, Answer and Response. This is a simple data model with several one-to-many relationships and no many-to-many relationships.

Briefly, a Game has a parent User and Theme. A Question has a parent Game and Category. An Answer has a parent Question. And a Response has a parent User, Question and Answer.

There's nothing too complex about this data model, so I'll focus on the conventions used to name the table primary and foreign key attributes. Each table has a PK named simply "Id" and each foreign key is prefaced by the parent entity name and "Id". For example, the Game entity has foreign key attributes "ThemeId" and "UserId".

To keep things simple and perhaps facilitate reporting or statistics down the line, all PKs and FKs are of type integer.

You can download the enclosed sql scripts to create the tables in either MSSQL or MySQL format. I chose to create proper FK constraints for the tables. If you prefer not to use foreign keys, feel free to remove them from the sql prior to running the scripts.

Next, we'll look at an Illudium template for generating a service, controller and Transfer Object decorator to support create, read, update and delete (CRUD) operations.

August 12, 2007

4 responses so far ↓

Michael Sharman - Aug 12, 2007 at 2:37 PM

Just a curious question on the ERD, could you not remove the QuestionId from the Response table because you have it from the Answer table?

So you'd have User -> Response -> Answer -> Question

As you have it now seems to break 3NF, but then again...when designing a database (particularly for the web) it's often good to have a little bit of redundant or duplicate data to make queries a little easier to manage!

I'm just curious on developers thoughts in these situation.

Paul Marcotte - Aug 12, 2007 at 10:24 PM

Hi Michael,

You are absolutely correct. Not sure why I chose to include the foreign key to the question table when it could in fact be derived from the parent Answer. I'll revisit this at the end of the series and perhaps add a post on refactoring as a finale.

Cheers!

Lola LB - Aug 14, 2007 at 5:51 AM

The download link isn't working . . .

Paul Marcotte - Aug 14, 2007 at 7:58 AM

Hi Lola,

Thanks for pointing that out. I mistakenly thought that I could have 2 enclosures, but not so. Instead you'll find both a MySQL and MSSQL sql script in a zip named trivia_sql.zip.

Leave a Comment

Leave this field empty: