Go Premium for a chance to win a PS4. Enter to Win


Managing fees in my ongoing designs

Posted on 2014-11-19
Medium Priority
Last Modified: 2014-12-09
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
Question by:sammySeltzer
  • 7
  • 6
LVL 40

Accepted Solution

PatHartman earned 1500 total points
ID: 40452696
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.
LVL 29

Author Comment

ID: 40452725
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?
LVL 40

Expert Comment

ID: 40452885
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.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 29

Author Comment

ID: 40452910
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.
LVL 40

Expert Comment

ID: 40455482
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.
LVL 29

Author Comment

ID: 40460867
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.
LVL 40

Expert Comment

ID: 40462599
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?
LVL 29

Author Comment

ID: 40462876
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.
LVL 40

Expert Comment

ID: 40462898
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.
LVL 29

Author Comment

ID: 40462948
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.
LVL 40

Expert Comment

ID: 40463026
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.
LVL 29

Author Comment

ID: 40463197
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.
LVL 40

Expert Comment

ID: 40463264
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question