November 18, 2010 at 5:29 am
Dear All,
I am looking for a database model that would meet the following criteria:
1) designed for (e.g.) house renting (each house will have a series of availability/unavailability periods)
2) optimized for search (look for houses available between date1 and date2)
Would anyone have such a model, together with the optimized query?
In advance, many thanks
Didier
November 18, 2010 at 5:52 am
If you want some help building it, please show us what you have so far.
If you want to hire someone to do it for you, what's your budget?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 18, 2010 at 6:16 am
Sounds to me like you have your logical design already. Houses and Time Periods. That should translate into tables (physical implementation) pretty easily. How familiar are you with database design?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 7:18 am
didier.boelens (11/18/2010)
Would anyone have such a model, together with the optimized query?
You have to be careful with questions like that. Database models are not a 1 Size Fits All thing. They are very unique to the business and environment they have been built for.
The second problem is that if someone does have such a model, likely they cannot share due to a "proprietary information" clause in their contracts. As in the company they built the model for owns it and sharing it would get them in legal hot water.
The third problem is that people don't tend to post database models for generic questions. DBAs / Data Architects invest a lot of time into building models. No one is going to just hand it over to someone for free.
So, to reiterate Gail's question. Are you just looking for advice on how to build one yourself, or would you like to hire someone to help you build one?
November 18, 2010 at 7:27 am
Some time ago a similar question turned up a number of times about a hospital bed allocation system. Turned out to be a course project.
Looked at the OPs posting history and very much doubt that is the case here. In fact by how much he jumps around technology and systems I suspect he would be able to deliver quite an interesting blog.
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 7:47 am
In fact I was thinking about something like the following:
see the following table definitions:
CREATE TABLE [dbo].[b]tbl_reservation[/b](
[key_id] [bigint] IDENTITY(1,1) NOT NULL,
[house_id] [bigint] NOT NULL,
[julian_day] [int] NOT NULL,
[status] [tinyint] NOT NULL,
CONSTRAINT [PK_tbl_reservation_1] PRIMARY KEY CLUSTERED
(
[key_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[b]tbl_house[/b](
[house_id] [bigint] IDENTITY(1,1) NOT NULL,
[reference] [varchar](50) NOT NULL,
CONSTRAINT [PK_tbl_house] PRIMARY KEY CLUSTERED
(
[house_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
as well as the following stored procedure and function
CREATE PROCEDURE [dbo].[stp_setReservationStatus]
(
@in_house_idbigint,
@in_start_datedate,
@in_end_datedate,
@in_statustinyint
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @julian_start integer = dbo.udf_dateToJulian(@in_start_date)
DECLARE @julian_end integer = dbo.udf_dateToJulian(@in_end_date)
WHILE @julian_start <= @julian_end
BEGIN
INSERT INTO tbl_reservation (house_id, julian_day, [status]) VALUES (@in_house_id, @julian_start, @in_status)
SET @julian_start = @julian_start + 1
END
END
CREATE FUNCTION [dbo].[udf_dateToJulian]
(
@in_datedate
)
RETURNS integer
AS
BEGIN
DECLARE @year int = YEAR(@in_date)
DECLARE @month int = MONTH(@in_date)
DECLARE @day int = DAY(@in_date)
RETURN 367 * @year - floor(7*(@year + floor((@month+9)/12))/4) - floor(3*(floor((@year+(@month-9)/7)/100)+1)/4) + floor(275*@month/9)+@day+1721028
END
-----------------------------------------
The principle is the following:
1) Use Julian dates (e.g. 01-NOV-2010 => 2455501, 09-NOV-2010 => 2455509)
2) Each time there is a reservation, I use the stp_setReservationStatus to insert as many "reservation" records (1 record = 1 day of reservation) as necessary
To look for houses with no reservation between 2 dates, I use the following query:
SELECT h.*
FROM tbl_house H
WHERE
NOT EXISTS (SELECT r.house_id FROM tbl_reservation r where r.house_id = h.house_id
AND
r.julian_day > dbo.udf_dateToJulian('2010-11-01')
and
r.julian_day < dbo.udf_dateToJulian('2010-11-04')
)
---------
This seems to be working but I was looking for something maybe more "friendly"
Would you have any idea?
Many thanks in advance,
November 18, 2010 at 7:50 am
Why are Julian dates necessary?
November 18, 2010 at 8:01 am
Instead of one row for each day the house is reserved, why not a start and end date as columns in one table?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 18, 2010 at 8:02 am
I use Julian Dates to obtain an integer value which makes the query faster...
Why would you have any other idea/solution?
November 18, 2010 at 8:04 am
What do you mean by "more friendly"? Shorter code? Interface? Readability?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 18, 2010 at 8:05 am
GSquared, because I can't find a way of building a SQL statement that would satisfy the following requirement:
I there anything available between date1 and date2.
If I take the following example:
reservation, between 05Nov2010 and 15Nov2010
If I am looking for houses which are free between 07Nov2010 and 17Nov2010, how can I build the query?
05Nov < 07Nov and 15Nov<17Nov...
Now if you have a solution, I will be more than happy
November 18, 2010 at 8:06 am
Stefan Krzywicki,
I mean that it will create many records for each reservation. Meaning the database will grow...
November 18, 2010 at 8:08 am
If you held the start and end dates for reservations the query would become
SELECT h.*
FROM tbl_house H
WHERE
NOT EXISTS (SELECT r.house_id FROM tbl_reservation r where r.house_id = h.house_id
AND
r.julianStartDay <= dbo.udf_dateToJulian('2010-11-04')
and
r.julianEndDay >= dbo.udf_dateToJulian('2010-11-01')
)
It means that hopefully your reservation table will be a lot smaller.
Either will do but this give an easily readable reservations.
The daily entries makes it easier to add constraints to the table though.
Often both are held with the daily being creaeted from the period reservations.
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 8:19 am
didier.boelens (11/18/2010)
GSquared, because I can't find a way of building a SQL statement that would satisfy the following requirement:I there anything available between date1 and date2.
If I take the following example:
reservation, between 05Nov2010 and 15Nov2010
If I am looking for houses which are free between 07Nov2010 and 17Nov2010, how can I build the query?
05Nov < 07Nov and 15Nov<17Nov...
Now if you have a solution, I will be more than happy
SELECT House
WHERE ReservationDate NOT BETWEEN '11/6/2010' AND '11/18/2010'
That will return places that are free on the 7th through the 17th inclusive.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
November 18, 2010 at 8:35 am
didier.boelens (11/18/2010)
I use Julian Dates to obtain an integer value which makes the query faster...Why would you have any other idea/solution?
If you insist on using an integer, you could format you date as YYYYMMDD instead. That will give you an 8 digit integer that is easy to visualize as a date.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply