How to model this classes/database tables?

Question

Hello.

I have to model classes and database tables for a "User" entity.

This "User" entity have this properties:

User
  Name
  Age
  Gender
  Email

But I have 2 user's type: "Paid users" and "Free users". Each one have his own properties:

Paid User
  Fee  
  Rate

Free User
  Expiration Date
  Level

"Paid User" and "Free User" are different, but both are "User".

I'm using ASP.NET MVC, NHibernate and Fluent Maps.

What is the best way and the right design pattern to model this classes and database tables?

Thank you very much!

Answer 1

I often find the best approach for this is to use an inheritance model. All common fields go into the User table. You create an entry in User first, and then use the resulting UserID to create additional entries in Paid User or Free User as necessary.

User 
  UserID
  Name 
  Age 
  Gender 
  Email 

Paid User 
  UserID <-- FK to User
  Fee   
  Rate 

Free User 
  UserID <-- FK to User
  Expiration Date 
  Level 

Then you can select (or create a VIEW) like this:

select u.UserID, U.Name, ...
    pu.Fee, pu.Rate,
    fu.ExpirationDate, fu.Level,
    case when pu.UserID is null then 0 else 1 end as IsPaidUser,
    case when fu.UserID is null then 0 else 1 end as IsFreeUser
from User u
left outer join PaidUser pu on u.UserID = pu.UserID
left outer join FreeUser fu on u.UserID = fu.UserID

Note: The above schema is pretty naive and doesn't handle cases where a user could be a free user, then a paid user, then a free user again. How you design for this really depends on your application and business rules.

Answer 2

What operations for theese user types?

Any design should start from operations then data structures, not conversely.

Answer 3

Assuming that in your Object model User would be considered abstract consider the following User table definition:

User
ID Name
Age
Gender
Email
Type <-- Free/Paid/etc

Paid User
ID
UserID <--- FK to User table
Fee
Rate

Free User
ID
UserID <--- FK to User table
Expiration Date
Level

This will allow you to query in this fashion:

Select * from User where Type = Free