Solved

Managing fees in my ongoing designs

Posted on 2014-11-19
13
82 Views
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:

FeeCategory:
FeeCategoryId
CategoryNames

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      
StatusId
Status

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
0
Comment
Question by:sammySeltzer
  • 7
  • 6
13 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
The third table brings all three together:

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

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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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
etc
etc

Is this what you have in mind?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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?
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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:

Hotels
hotelId
HotelName

Rooms
RoomID
RoomName
Some other stuff

Status
StatusId
Unincorporated
Incorporated
Profit
Non Profit
City Resident
Non Resident

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

Reservations
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
Comment Utility
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:

Unincorporated,
Incorporated      ,
Non  Resident,
Non Profit,
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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 …

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now