I am putting together a small inventory management system for an accommodation contract. I'm working for a Destination Management company and we have contracts from different hotels that enables us to sell their rooms to our clients.
- Base contract table - this is a year-round contract and will have an allocation
- Special promotion table - this a discounted rate under a base contract, sometimes the allocation will come from the base contract, meaning that it is shared and sometimes there will be cases that the special promotion will have its own allocation
- Inventory table - this is where we define the allocation of the contract
How can I design this in a database?
This would have work if a special promotion will always use the inventory of base contract but now I need to be able to have an inventory for special promotion that has its own allocation. Booking data will have both base_contract_id and special_promotion_id, special_promotion_id could be empty if the booking was sold under base contract only.
