Handling tables of different types in ASP.NET MVC

Question

I am designing a database for an ASP.NET MVC application. I'm an expert in neither, and I'm curious what the best approach would be for the following database snippet:

We will be storing Events in our database. Each event will be of a different type with various fields exclusive to one or some of the types. An example:

Events
* Id
* EventTypeId
* AllEventTypes_Field

EventType0
* EventId (FK)
* EventType0_Field

EventType1
* EventId (FK)
* EventType1_Field

We'll have a handful of event types in the end. I am tempted to put all fields into one large Events table, with nullable fields where appropriate. Or, we can separate out the tables into Supertype/Subtype: Events, EventType1, EventType2, etc (As above).

For the database design portion, I want to choose what makes the most sense and is "easiest" for the MVC framework. Essentially: what path will yield the least amount of headaches? :)

For super/sub types, would it be a matter of dragging over the Events table and creating classes for each subtype off of the main Events table?

Do you know if you're using Linq2SQL or Entity Framework for your database ORM objects? You should have selected one when you added it to your project. — TJB
– TJB, Commented Jan 29, 2010 at 21:37
LINQ. Though honestly, I can't say for sure how LINQ2SQL and EF differ. Does it make a world of difference? Sorry for the newboe reply :) — Dan
– Dan, Commented Jan 30, 2010 at 4:19

Robert Harvey · Accepted Answer · 2010-01-29 21:32:38Z

1

This Microsoft article discusses optional one-to-one relationships, which is essentially what you are describing:

http://msdn.microsoft.com/en-us/library/dd326769(VS.85).aspx

answered Jan 29, 2010 at 21:32

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dan Over a year ago

Thanks for the link, Robert. I'm also looking for information on how LINQtoSQL and/or EF can handle what that article is talking about.

Robert Harvey Over a year ago

Creating classes in Linq to SQL or EF is a simple matter of dragging the tables over to the Linq to SQL designer from the Server Explorer. A one to one link should automatically be established between the classes. In your code, the link should be accessible like this: MyEvent.EventType1.EventType1_Field

Dan Over a year ago

Say I abandon the 1:1 link and have one large table. Does it make sense to then break the table up into separate subtypes/LINQ classes? I bring this up as there are some properties that exist on maybe 1/2 of the subtypes - so I'm tempted to just have one large database table, but have multiple classes in my data layer - feasible/possible? Many thanks for the input, Robert :)

Robert Harvey Over a year ago

Unless you have separate tables I don't see anything to be gained by having separate classes.

Collectives™ on Stack Overflow

Handling tables of different types in ASP.NET MVC

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related