Results 1 to 7 of 7
  1. #1
    justivan is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3

    Database Design Help

    Hello Community!


    I'm currently working for a Destination Management Company where we have an internal team that processes the the reservations and keeping the records on an excel workbook on a daily basis. Meaning that there are hundreds of workbooks and it needs to be summarized on a weekly basis for weekly production report. Now, I have managed to summarize all the workbooks using Power Query but the problem is I find using excel for our use case is not great and will keep getting harder to maintain in the long run. I'm thinking of transferring our records in an Access database and I'm not good with database designs and therefore need any help I could get from here to design the database properly. I created an ERD for the initial design that I could think of. Fields in the left table (reservations) are all from our company's system and the 'rates' table is the one that's going to be populated by our users. Currently, the excel template that we are using is consist of similar fields of the rates table below, they enter the applicable rates then there's one column at the end 'total_cost' which is formulated based on the rates and the dates (in_date and out_date). Of course when there is cost there's also a 'total_sales'. Sales is formulated as well after adding the applicable mark up. My struggle is how to go about handling the split in the seasonality. Basically put, there can be a period where rates will change (screenshot below). How we currently handle this is by making a copy of the record and updating the dates then in the summary report, I'm just aggregating everything to make it one record. Question is, how would you implement a database to handle this kind of scenario? Discounts and Markup can vary as well depending on the requirements of our commercial team. I'm thinking of storing the data per day. So basically I'll have a date field in 'rates' table and have a form where they can split the dates. Is this doable in MS Access? Perhaps I'll need VBA to handle the split before sending the update query to the database? Please let me know your thoughts. Thank you!


    Click image for larger version. 

Name:	sample.png 
Views:	41 
Size:	7.7 KB 
ID:	43994
    Case where the record needs to be split because of the change in daily rate.

    Click image for larger version. 

Name:	Bookings Model.png 
Views:	38 
Size:	25.8 KB 
ID:	43995
    Initial design the I could think of.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    struggling to follow the logic of your tables v your requirement

    rates - surely they need a from date and an end date? Also recommend you have a PK field as well. And use meaningful names - Res_ID, Rate_ID for example

    your top image includes a discount field - don't see that in your tables.

    determining what rates to pick is fairly straight forward - you want all the rates where the end date is >=in date and the from date <=out date.

    you can get away without an end date providing the business rule allows for it, a slightly different calculation

  3. #3
    justivan is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Hi Ajax,

    Thanks for your thoughts.
    Quote Originally Posted by Ajax View Post
    rates - surely they need a from date and an end date? Also recommend you have a PK field as well. And use meaningful names - Res_ID, Rate_ID for example
    Yes they indeed need a from/end date. I just did not included it yet because I'm not sure yet if I should store the rates with a range of date or store it per date.

    Quote Originally Posted by Ajax View Post
    your top image includes a discount field - don't see that in your tables.
    Same here. I'm not sure yet if I should store it or just handle the discount from the front-end and then store the discounted rate in the database. Reason being is that the discount may be applicable to the room only and not applicable to other charges like meal, child etc. It depends on the offers of the hotels.

    Quote Originally Posted by Ajax View Post
    determining what rates to pick is fairly straight forward - you want all the rates where the end date is >=in date and the from date <=out date
    I'm not sure I understood this. Do you mean when querying to get a report? Rates will be manually entered by the user and there will be cases that they need to split the dates because of changes in rate per seasonality. Say for example, the travel dates of the booking is from 22 Jan - 30 Jan, from 22 Jan - 26 Jan rate is 200 and from 27 onwards rate is 300.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Do you mean when querying to get a report?
    yes or auto completing a form or a general enquiry.

    You need a lot more tables

    tblHotels

    tblRooms

    tblRates

    tblGuests

    tblBookings

    to name just a few
    Rates will be manually entered by the user
    Really? do they make that up as they go along or do they look it up somewhere?

    Think of it this way

    You have a number of hotels
    each hotel has a number of rooms
    each room has a number of features (single/double/family, executive/standard/sea view/landside view, Bed only/B&B/half board/full board etc)
    each feature is bundled to a rate or rates (some features such as level of board may be an add on charge rather than part of the room rate
    each rate will vary based on time of year and or day of the week
    each rate may be subject to discounts - perhaps based on number of days/overall charge/special offer etc

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I had an hour or so to kill so I've put this simple db together for you to demonstrate how you can show the different rates over the period of a stay. It's been done quickly so expect a few things to not work as expected.bookingrates.zip. The form will open to a booking form. There is a button to take you to the hotel maintenance form where you can add rooms and rates. No form for clients, just open tblClients and add some more names if you wish.

    There is only one query to use for this demo.

    Navigation is basic - use the standard access functionality (record navigation at bottom)

    This does not show things like room availability and not all data is populated - see Town House

    take a look at the relationships to see what I was suggesting.
    Click image for larger version. 

Name:	image_2021-01-23_003851.png 
Views:	31 
Size:	28.5 KB 
ID:	44003

    If you accept the basic design, you will need to develop an import routine which will populate the tables with the appropriate values.

  6. #6
    justivan is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Hi Ajax,

    Thanks a lot for your input. I appreciate the design and I'm starting to formulate ideas on top of my head. I made some points to further elaborate the problem that I'm trying to solve.

    What do I want to get out of the tool / What problem I'm trying to solve
    - The idea is to import the list of bookings that we are getting from our in-house system on a daily basis to an Access database.
    - Have a form to enter the applicable rates by our team for each bookings.
    - Ease of maintaining data as it will be placed in one database instead of hundreds of excel workbook.
    - Since the all records is in one place, it will be easier to update a record rather than going through hundreds of excel workbooks.
    - More control of how the users enter the data (i.e. validations) to make sure all inputs are consistent.
    - Ease of generating daily/weekly reports.


    What do you have to put into the tool?
    - Daily booking list that we are getting from our in-house system it's in a .csv format
    - Applicable rates for each bookings


    Current workflow -
    1. Download the booking list from our in-house system (csv format)
    2. Copy the list and paste it to the excel template
    3. Populate the excel workbook with applicable rates that can be found in our in-house system.
    4. Send the completed excel workbook to the reservations team.


    Basically, the data that will go to 'reservations' table is already predefined. So what I do think is that it will not be necessary to normalize the 'reservations' table and break it down to multiple tables. The idea is, instead of the team putting the booking list to an excel workbook, it will be instead imported in an Access database. From there, a form will be served for them to populate with it the applicable rates which will be stored to a different table and the ref_id will be the key identifier.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It's your business, you understand it, I am guessing based on previous experience developing booking systems.

    From your description of the workflow, I'm surprised your in house system cannot produce the files you require. Excel will open csv files. At the very least you should be able to extract the rates your users are currently having to look up

    You still have your excel head on and I'm not interested in making denormalised data work in a database so best if I drop out now, perhaps someone else will step in.


    Good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-23-2018, 10:25 PM
  2. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  3. Replies: 3
    Last Post: 01-13-2017, 03:52 PM
  4. Replies: 2
    Last Post: 01-28-2013, 04:42 PM
  5. Database Design for Specification Database
    By khwaja in forum Database Design
    Replies: 2
    Last Post: 11-24-2011, 03:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums