Datamodel for a MVC learning project

I am trying to learn Microsoft MVC 2, and have in that case found a small project I wanted to deploy it on.

My idea was to simulate a restaurant where you can order a tab开发者_JS百科le.

Basics:

• A user can only reserve a full table, so I don't have the trouble of merging people on different tables.

  A person can order a table for a certain amount of hours.

My question was how I could make the data model the smartest way. I thought of just having a my database like this:

Table { Id, TableName }

Reservations { Id TableId ReservedFrom ReservedTo UserId }

User { UserId UserName ... }

By doing it this way I would have to program a lot of the logic in e.g. the business layer, to support which tables are occupied at what time, instead of having the data model handle it.

Therefore do you guys have a better way to do this?

A database constraint that doesn't allow two reservations for a table to overlap using a function that counts the number of reservations for the table whose start datetime or end datetime is between the datetimes of the row being inserted. The constraint would ensure that the count is 1 (the row just inserted).

Also, you should have your user interface block times where all of the tables available are reserved. Essentially, you'd get all the reservations for the day and for each hour block count the number of reservations that span that block -- if the count is equal to the number of tables, then the UI doesn't allow that block to be chosen. This spans your business/UI layers.

There's no way to know how long a person will take to eat so you cannot assume the ReservedTo time is accurate. I would build up timeslots in a seperate table. That way a simple unique constraint could be used.

TimeSlot { id, StartTime, Duration }

Additionally I would dump the user table and just put in a name.

Reservation { id, tableId, date, timeSlotId, Name }

put the unique constraint on { tableId, date, timeSlotId }

This could be expanded out to allow different durations for different tables but that is outside tke scope of a learning project I think.