Search code examples
c#asp.netentity-frameworkdatabase-designscaffolding

Robust billing system design. Am I going completely in the wrong direction?

I am developing a hotel registration/billing application in ASP.Net, using the entity framework. When it comes to generating the bill for the registrant, my client has a lot of wacky criteria. Instead of a per bed per night (pretty standard..) billing system, the customer could be billed a different amount based on their age, the specific dates they are visiting, the room type (double bed, single bed, etc) or the conference that they're attending, or all four...

Here's my diagram: http://s8.postimg.org/w8nxh8qlh/abstraction.jpg

Being a relatively young coder, I feel like I'm missing something, somewhere along the "Rule" table. What I'm trying to do, is allow an administrator to create any number of ConditionalStatements ("Age > 5" or "Age < 10" where "Age" is the Statement.Value, '>' is the Condition.Value, and '5' is the ConditionalStatement.Value) and link any number of them together, thus creating a "Rule". The administrator would then associate the Rule with various RateSchedules, and the application would then have the ability to generate a bill for any customer that has registered for a stay..

I feel like I'm on the right track, and that the majority of this solution will work in every way that I need it to, but something doesn't seem right. I have two questions relating to "Rule" creation..

  • What am I doing wrong, as far as the "Rule" design goes? It feels wrong for me to be generating a new RuleSet.ID each time the admin wants to make a new Rule.. Is this something I should be doing programmatically? Should I completely rethink this Rule process entirely?

  • From an Entity Framework / scaffolding perspective, what's the best way for me to be adding new Rules to the db? Default scaffolding allows me to insert one row at a time, but to actually create a Rule, I would need to associate a RuleSet.ID with multiple ConditionalStatement.IDs -- but I can't figure out a way to do this intuitively with EF scaffolding..

Any pointers in the right direction are highly appreciated! Thanks :)

EDIT: I wound up redesigning based on the responses to my question, but during research stumbled across Microsoft's business rule engine: http://msdn.microsoft.com/en-us/library/aa561216.aspx and also a mathematical expression evaluator: http://ncalc.codeplex.com/ Just in case anyone else finds this question in the future and these links can help. Thanks for all the help guys!

Solution

  • Modelling your domain in the DB is usually a bad practice. The DB should be a detail and all your logic should be in the code. To do this, you should do a design to figure out the requirements from the client and create a domain on top of that. At this stage persisting them will be very simple.

    For example, imagine a PricingRule abstract class and many different implementations like AgePricingRule, DatePricingRule, RoomTypePricingRule. Then your Bill object can take the PricingRules and apply them and get the final price.

    The amount of complexity of the data that needs to be stored will be trivial, just some configuration for the pricing rules.