Managing fees in my ongoing designs

Hi again, mates.

Hopefully, this is the last piece of the puzzle for this issue.

Here is  a spreadsheet of fees information.

Status	         Rental Fee 	 Extra Hour Fee  Deposit Fee 	Cancel Fee	Key Deposit
Unincorporated	 $275.00 	 $50.00 	 $275.00 	 $25.00 	 $-   
Incorporated	 $300.00 	 $55.00 	 $300.00 	 $25.00 	 $-   
Non  Resident	 $350.00 	 $80.00 	 $350.00 	 $25.00 	 $-   
Non Profit	 $300.00 	 $55.00 	 $300.00 	 $25.00 	 $-   
Profit	         $350.00 	 $80.00 	 $350.00 	 $25.00 	 $-   
Unincorporated	 $125.00 	 $60.00 	 $125.00 	 $25.00 	 $-   
Incorporated	 $200.00 	 $75.00 	 $200.00 	 $25.00 	 $-   
Non  Resident	 $225.00 	 $85.00 	 $225.00 	 $25.00 	 $-   
Non Profit	 $125.00 	 $60.00 	 $125.00 	 $25.00 	 $-   
Profit	         $300.00 	 $100.00 	 $300.00 	 $25.00 	 $-   
Unincorporated	 $175.00 	 $35.00 	 $175.00 	 $25.00 	 $100.00 
Incorporated	 $215.00 	 $40.00 	 $215.00 	 $25.00 	 $100.00 
Non  Resident	 $275.00 	 $65.00 	 $275.00 	 $25.00 	 $100.00 
Non Profit	 $215.00 	 $40.00 	 $215.00 	 $25.00 	 $100.00 
Profit	         $275.00 	 $65.00 	 $275.00 	 $25.00 	 $100.00 

Open in new window

As you can see, there are different fees for different status.

We have the unincorporated, incorporated, resident, non resident, profit, non profile.

Then the fees are for rental, extra hour, deposit, cancellation, and key deposits.

The fees are for Room rentals.

I have the table design done for rooms.

What  I am trying to fix is how to normalize these fees and the various groups they apply to.

Now, I have the following table:


and sample data is:

Fee Category Id                        Category Name
1                                                   Rental Fees
2                                                   Extra Hour Fees
3                                                   Deposit Fees
4                                                   Cancellation Fees
5                                                   Key Deposit Fees

Status table      

Sample Data:
Status ID                                Status
1                                              Unincorporated
2                                              Incorporated
3                                              Non Resident
4                                              For Profit
5                                              Non Profit

This is where I am stuck.

1, I still need to handle the Fees and link them to both Status table and Category Fees
2, I still need to link the Rooms to one of these tables but which one?

DB Design continues to be a weakness.

Thanks for your kind help
LVL 29
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The third table brings all three together:

RoomRateID (autonumber PK)
RoomID (foreign key to tblRooms)
CategoryID (foreign key to tblCategories)
StatusID (foreign key to tblStatus)

Don't forget to establish RI so this table doesn't end up with orphans.  You may want cascade delete also but that really depends on how the app works.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sammySeltzerAuthor Commented:
Hi Pat,

Thanks for the prompt response.

Ok looking your design, a sample data would be:

RM RateId    RmID    CatID    StatusId    RoomRate
1                      2            5           3               $275.00
2                      2            2           3               $300.00

Is this what you have in mind?
Looks like what I pictured.  You'll end up with a lot of rows.  You also need to decide how to handle sparseness.  Are you going to create a row for every combination of the three foreign keys or are you going to leave some out?   You might want to do a smaller table with just Category and Status if they can simply be added to the basic room rate.  Then you would have a rooms table with basic rates and then lookup up by category/status for extra charges.
Put Your Flow Data to Work

SolarWinds® Flow Tool Bundle combines three easy-to-download, easy-to-use flow analysis tools that can help you quickly distribute, test, and configure your flow traffic.

sammySeltzerAuthor Commented:
When you say basic rates, do you mean the rental fees?

If I do just Category and Status, then there has to be another table to bridge Category and Status because with those two, we have  a Many to Many relationship situation.

Also, I am not sure if it is a good idea to add the categoryId and statusId to Rooms table.

See why I am confused?

What would be ideal is to organize this in such that all I have to do is query the tables, joining them and creating a VIEW from all the joins.

Then all I would need to do with my app is manipulate the VIEW.
Only you know what the requirements are.  I suggested an alternative that simplifies the managing of the fees but that only works if that is how your fee schedule works.  Based on the current values for category and status, you would have 25 rows for each and every room.  If the fees are truly different for each room, then you have no alternative.    Or, you could have one table for rooms with the basic fee for the room and a second table with 25 rows for the additional fees.

You don't actually join these tables.  The selection is managed in the form.  First you choose a room, then you enter the category and the status and your combos, look up the additional fees.  and add them as child records to the reservation.  You save the fee amounts with the reservation because they change over time and to manage that, you would need to add effective and termination dates for each separate fee so you could lookup what the fee was at a particular point in time.
sammySeltzerAuthor Commented:
I thought my design shed some light on my requirements even though it is not explicitly stated.

The requirements is that a client wants to rent a room.

We need to determine whether the client is a resident. There are different fees for different potential clients depending on whether the client is a resident, running a profit or non profit business, from incorporated or incorporated city, etc.

Besides these requirements, how to turn the contents of the spreadsheet into a normalized relationship, I think, doesn't require that you know the requirements.
What is missing is the relationship between the room and the fees.  Are the fees specific to the room or can the fees stand alone and be added to the room fee?  The answer determines the structure of the tables.  Will you have one table with 25 rows per room or will you have two tables.  One for rooms and one with only 25 total rows for fees?
sammySeltzerAuthor Commented:
The fees are specific to the rooms.

The way they explained it,  there are 4 groups as stated earlier.

Each group has its own fees (rental, deposit, after hour, key).

So, definitely the fees need to be specific to each room.

I am actually, right now, following your original suggestion to have 25 rows per room.

At the end, I will like to join all the tables to create a view which should be more manageable.

The one point I would like to point out is that we are not just using these table for inserting records.

There must be a relationship between the tables we are creating.

For instance, assume that customer A wishes to make a reservation for November 28, 2014 at 4PM but that day or that time slot is not available, we were told to NOT tell the customer that that time slot is taken.

Rather, they want us to display one month's data, including the time slot that is already taken.

This way, the customer can see that that time slot is not available but also see alternative, available time slots.

I can code this to work per their specs. I just want to make sure the DB is designed to allow me to present them available dates if their preferred date is not available.

So, hopefully, what you and I are doing is the proper design.

Thanks for your help Pat.
Make sure the client understands the ramification of associating the fees with each room.  That being, there is no such thing as a generic fee change.  If you want to change the Extra Hour fee from $50 to $51 for unincorporated status, then that fee must be changed for EVERY room it applies to.  It is hugely more difficult to update/manage the table with 25 rows per room than it would be if the fees were separate.
sammySeltzerAuthor Commented:
That's true but understand that there are *FIVE* different fee categories.

Again, it goes back to my original concern of posting here hoping to get DB design expert help and happy for your assistance but if I can understand how to break these down and be able to relate them, that would be the best thing to do.

If I were to go the other route of having separate Fees table, how do you recommend I approach this?

The good news is that I have not started coding.

So, there is ample opportunity to do this right.

Current, I have the following:


Some other stuff

Non Profit
City Resident
Non Resident

Fees (the one we are currently working on)
Fees (this is where we have to do it 25 times with feeId) because each status has 5 different fees.

This is where customers's reservation details are saved.

So, as it stands now, the only sticking point that I see is the potential anomalies with the Fees table.
Why does the Status table have a column for each status value?  Shouldn't those be rows?

You can't arbitrarily switch between designs and I can't tell you which to use.  I can tell you which I think is most likely or easier to work with but schema design is ultimately based on business rules.  You need to examine the business rules and make sure you understand them correctly.  I am emphasizing this because the client may not realize until too late if the rule is implemented incorrectly.  So the CLIENT must understand the various options and tell you which his business uses.  Does he actually come up with a different fee schedule for each and every room?  Unlikely?  He probably doesn't even have a different rate for each room.  More likely, rooms have a category and the rate is applied to the category.
sammySeltzerAuthor Commented:
Ok, I think we are getting to the point now where it is now more confusing than it is intended to.

Take another look a sample (real data) spreadsheet I posted initially.

There are five different types of fees:

Rental Fee,
Extra Hour Fee,
Deposit Fee,
Cancel Fee,
Key Deposit

Take another look again, there are five different categories or statuses as I called them on my table:

Incorporated      ,
Non  Resident,
Non Profit,

Depending on your status, you get a different fee (Rental Fee,Extra Hour Fee,Deposit Fee,Cancel Fee,
Key Deposit).

Finally, and most importantly, these graphics I posted earlier:

Unincorporated   $275.00         $50.00          $275.00         $25.00          $-   
Incorporated     $300.00         $55.00          $300.00         $25.00          $-   
Non  Resident    $350.00         $80.00          $350.00         $25.00          $-   
Non Profit       $300.00         $55.00          $300.00         $25.00          $-   
Profit           $350.00         $80.00          $350.00         $25.00          $-   

Unincorporated   $125.00         $60.00          $125.00         $25.00          $-   
Incorporated     $200.00         $75.00          $200.00         $25.00          $-   
Non  Resident    $225.00         $85.00          $225.00         $25.00          $-   
Non Profit       $125.00         $60.00          $125.00         $25.00          $-   
Profit           $300.00         $100.00         $300.00         $25.00          $-   

Unincorporated   $175.00         $35.00          $175.00         $25.00          $100.00 
Incorporated     $215.00         $40.00          $215.00         $25.00          $100.00 
Non  Resident    $275.00         $65.00          $275.00         $25.00          $100.00 
Non Profit       $215.00         $40.00          $215.00         $25.00          $100.00 
Profit           $275.

Open in new window

clearly shows there are different fees for different rooms and again, each room is priced differently depending on status or category.

Looking at this sample again, you can see, just for this example that there are 3 rooms, each room has different prices for each status or category.

If you apply this to real life and this is real life, you go to a hotel, you don't get same price for every room. Some rooms are priced higher or lower than others. Same thing here.

This might be confusing to me because I can readily admit that I really do struggle when it comes to design but a true modeler shouldn't have issues with this.
A good data modeler, someone usually better than me,  does NOT need to see my business rules to help model data based on the spreadsheet.

This guy, Scott Pletcher has often saved me in situations like this but unfortunately, he must have been real busy this time around.
A good data modeler, someone usually better than me,  does NOT need to see my business rules to help model data based on the spreadsheet.
Apparently I'm not good enough to help you.  I left my Ouija Board at home today.  Perhaps Scott will show up.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.