<p>I've built one, but I'm convinced it's wrong.</p>

<p>I had a table for customer details, and another table with the each date staying (i.e. a week's holiday would have seven records).</p>

<p>Is there a better way?</p>

<p>I code in PHP with MySQL</p>

[{'answer_id': 67443, 'author': 'Joel Martinez', 'author_id': 5416, 'author_profile': 'https://Stackoverflow.com/users/5416', 'pm_score': 0, 'selected': False, 'text': "<p>What's wrong with that? logging each date that the customer is staying allows for what I'd imagine are fairly standard reports such as being able to display the number of booked rooms on any given day.</p>\n"}, {'answer_id': 67446, 'author': 'nickf', 'author_id': 9021, 'author_profile': 'https://Stackoverflow.com/users/9021', 'pm_score': 3, 'selected': False, 'text': '<p><a href="http://www.databaseanswers.org/data_models/hotels/hotel_reservations_popkin.htm" rel="nofollow noreferrer">Here you go</a></p>\n\n<p>I found it at this page:\n<a href="http://www.webmastersbydesign.com/2008/08/04/library-of-over-500-free-database-models/" rel="nofollow noreferrer">A list of free database models</a>.</p>\n\n<p><strong>WARNING</strong>: Currently (November \'11), Google is reporting that site as containing malware: <a href="http://safebrowsing.clients.google.com/safebrowsing/diagnostic?client=Firefox&amp;hl=en-US&amp;site=http://www.databaseanswers.org/data_models/hotels/hotel_reservations_popkin.htm" rel="nofollow noreferrer">http://safebrowsing.clients.google.com/safebrowsing/diagnostic?client=Firefox&amp;hl=en-US&amp;site=http://www.databaseanswers.org/data_models/hotels/hotel_reservations_popkin.htm</a></p>\n'}, {'answer_id': 67469, 'author': 'Sam', 'author_id': 9406, 'author_profile': 'https://Stackoverflow.com/users/9406', 'pm_score': 0, 'selected': False, 'text': '<p>The answer heavily depends on your requirements... But I would expect only storing a record with the start and stop date for their stay is needed. If you explain your question more, we can give you more details.</p>\n'}, {'answer_id': 67471, 'author': 'Shaun Austin', 'author_id': 1120, 'author_profile': 'https://Stackoverflow.com/users/1120', 'pm_score': 1, 'selected': False, 'text': "<p>Some questions you need to ask yourself:</p>\n\n<ul>\n<li>Is there a reason you need a record for each day of the stay?</li>\n<li>Could you not just have a table for the stay and have an arrival date and either a number of nights or a departure date?</li>\n<li>Is there specific bits of data that differ from day to day relating to one customer's stay?</li>\n</ul>\n"}, {'answer_id': 67472, 'author': 'Peter Bailey', 'author_id': 8815, 'author_profile': 'https://Stackoverflow.com/users/8815', 'pm_score': 0, 'selected': False, 'text': '<p>A tuple-per-day is a bit overkill, I think.  A few columns on a "stay" table should suffice.</p>\n\n<pre><code>stay.check_in_time_scheduled\nstay.check_in_time_actual\nstay.check_out_time_scheduled\nstay.check_out_time_actual\n</code></pre>\n'}, {'answer_id': 67482, 'author': 'Rob Gray', 'author_id': 5691, 'author_profile': 'https://Stackoverflow.com/users/5691', 'pm_score': 0, 'selected': False, 'text': '<p>Is creating a record for each day a person stays neccessary?  It should only be neccessary if each day is significant, otherwise have a Customer/Guest table to contain the customer details, a Booking table to contain bookings for guests.  Booking table would contain room, start date, end date, guest (or guests), etc.</p>\n\n<p>If you need to record other things such as activities paid for, or meals, add those in other tables as required.</p>\n'}, {'answer_id': 67504, 'author': 'Asaf R', 'author_id': 6827, 'author_profile': 'https://Stackoverflow.com/users/6827', 'pm_score': 0, 'selected': False, 'text': '<p>One possible way to reduce the number of entries for each stay is, store the time-frame e.g. start-date and end-date. I need to know the operations you run against the data to give a more specific advice.</p>\n\n<p>Generally speaking, if you need to check how many customers are staying on a given date you can do so with a stored procedure. </p>\n\n<p>For some specific operations your design might be good. Even if that\'s the case I would still hold a "visits" table linking a customer to a unique stay, and a "days-of-visit" table where I would resolve each client\'s stay to its days.</p>\n\n<p>Asaf.</p>\n'}, {'answer_id': 68287, 'author': 'Lea de Groot', 'author_id': 10648, 'author_profile': 'https://Stackoverflow.com/users/10648', 'pm_score': 0, 'selected': False, 'text': "<p>You're trading off database size with query simplicity (and probably performance)</p>\n\n<p>Your current model gives simple queries, as its pretty easy to query for number of guests, vacancies in room X on night n, and so on, but the database size will increase fairly rapidly.</p>\n\n<p>Moving to a start/stop or start/num nights model will make for some ... interesting queries at times :)</p>\n\n<p>So a lot of the choice is to do with your SQL skill level :)</p>\n"}, {'answer_id': 68383, 'author': 'DancingFool', 'author_id': 10630, 'author_profile': 'https://Stackoverflow.com/users/10630', 'pm_score': 1, 'selected': False, 'text': '<p>Some things that may break your model.  These may not be a problem, but you should check with your client to see if they may occur.</p>\n\n<ul>\n<li>Less than 1 day stays (short midday stays are common at some business hotels, for example)</li>\n<li>Late check-outs/early check-ins. If you are just measuring the nights, and not dates/times, you may find it hard to arrange for these, or see potential clashes. One of our clients wanted a four hour gap, not always 10am-2pm.</li>\n</ul>\n'}, {'answer_id': 68461, 'author': 'Justin', 'author_id': 43, 'author_profile': 'https://Stackoverflow.com/users/43', 'pm_score': 0, 'selected': False, 'text': '<p>I don\'t care for the schema in the diagram. It\'s rather ugly.</p>\n\n<p><strong>Schema Abstract</strong></p>\n\n<p><strong>Table: Visit</strong></p>\n\n<p>The Visit table contains one row for each night stayed in a hotel.</p>\n\n<p>Note: Visit contains</p>\n\n<ul>\n<li>ixVisit</li>\n<li>ixCusomer</li>\n<li>dt</li>\n<li>sNote</li>\n</ul>\n\n<p><strong>Table: Customer</strong></p>\n\n<ul>\n<li>ixCustomer</li>\n<li>sFirstName</li>\n<li>sLastName</li>\n</ul>\n\n<p><strong>Table: Stay</strong></p>\n\n<p>The Stay table includes one row that describes the entire visit. It is updated everytime Visit is udpated.</p>\n\n<ul>\n<li>ixStay</li>\n<li>dtArrive</li>\n<li>dtLeave</li>\n<li>sNote</li>\n</ul>\n\n<p><strong>Notes</strong></p>\n\n<p>A web app is two things: SELECT actions and CRUD actions. Most web apps are 99% SELECT, and 1% CRUD. Normalization tends to help CRUD much more than SELECT. You might look at my schema and panic, but it\'s fast. You will have to do a small amount of extra work for any CRUD activity, but your SELECTS will be so much faster because all of your SELECTS can hit the Stay table.</p>\n\n<p>I like how Jeff Atwood puts it: "Normalize until it hurts, denormalize until it works"</p>\n\n<p>For a website used by a busy hotel manager, how well it works is just as important as how fast it works.</p>\n'}, {'answer_id': 71183, 'author': 'J.D. Fitz.Gerald', 'author_id': 11542, 'author_profile': 'https://Stackoverflow.com/users/11542', 'pm_score': 2, 'selected': False, 'text': '<p>I work in the travel industry and have worked on a number of different PMS\'s. The last one I designed had the row per guest per night approach and it is the best approach I\'ve come across yet.\nQuite often in the industry there are particular pieces of information to each night of the stay. For example you need to know the rate for each night of the stay at the time the booking was made. The guest may also move room over the duration of their stay.</p>\n\n<p>Performance wise it\'s quicker to do an equals lookup than a range in MySQL, so the startdate/enddate approach would be slower. To do a lookup for a range of dates do "where date in (<em>dates</em>)".</p>\n\n<p>Roughly the schema I used is:</p>\n\n<pre><code>Bookings (id, main-guest-id, arrivaltime, departime,...)\n\nBookingGuests (id, guest-id)\n\nBookingGuestNights (date, room, rate)\n</code></pre>\n'}, {'answer_id': 76025, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': "<p>Wow, thanks for all the answers. </p>\n\n<p>I had thought long and hard about the schema, and went with a record=night approach after trying the other way and having difficulty in converting to html.</p>\n\n<p>I used CodeIgniter with the built in Calendar Class to display the booking info. Checking if a date was available was easier this way (at least after trying), so I went with it. But I'm convinced that it's not the best way, which is why I asked the question.</p>\n\n<p>And thanks for the DB answers link, too.</p>\n\n<p>Best,</p>\n\n<p>Mei</p>\n"}]
['https://Stackoverflow.com/questions/67421', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']