Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Let me try to summarize your question to see if I understand it properly:</p> <blockquote> <p>You have a set of Hotels. Each Hotel has a set of Rooms. Each Room belongs to one of a number of possible Room Types. The lowest level of detail we're interested in here is a Room.</p> </blockquote> <p>This suggests a table of Hotels, a lookup table of Room Types, and a table of Rooms: each Room will have a reference to its associated Hotel and Room Type.</p> <blockquote> <p>For any given day, a room is either booked (sold) or not booked (let's leave off partial days for simplicity at this point). For each day in the year before and the year after the current day, you wish to know how many rooms of each type were available (non-booked) at each hotel.</p> </blockquote> <p>Now, since hotels need to be able to look at bookings individually, it's likely you would maintain a table of bookings. But these would typically be defined by a Room, a Start Date, and a number of Nights, which isn't ideal for your stated reporting purposes: it isn't broken down by day.</p> <p>So you may wish to maintain a "Room Booking Log" table, which simply contains a record for each room booked on each day: this could be as simple as a datestamp column plus a Room ID.</p> <p>This sort of schema would let you generate the output you're describing relatively easily via aggregate queries (displaying the sum of rooms booked per day, grouped by hotel and room type, for example). The model also seems like it would lend itself to an <a href="http://en.wikipedia.org/wiki/Olap_cube" rel="nofollow">OLAP cube</a>.</p>
    singulars
    1. This table or related slice is empty.
    1. PONeed ideas/advices about a database structure
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USDan J
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PONeed ideas/advices about a database structure
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO| room_id | hotel_id | date | count_we_have | this will generate (if we have 150 hotels and 3 room types each, 150*3*(365*2)=328500 lines, is it ok? each room type will add 730 new lines? did i understand right?
      singulars
      1. PO
      1. USMustafa
    2. COI assume the first column in your example should be `room_type_id`? Adding a room type to that equation would add 730 records *per hotel*, i.e. ~100000 records. "Is it ok?" doesn't really have a canonical answer, but 300-400k records, if queried and indexed appropriately, isn't unreasonable for a professional DBMS to handle (in my non-expert opinion). Obviously, **be careful** if you end up adding large numbers of room types or many more years of data, as the table's size will increase rapidly...
      singulars
      1. PO
      1. USDan J
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload