Question

Prabhat_IE on Mon, 18 Feb 2013 11:18:28


Hello,

I have a scenario where the application need to store “users details (service providers)” and their “service type (ex: plumbing work)” and “available/free time (ex: Every day 3:00 pm to 5:00 pm or Sunday 4:00pm to 8:00 pm etc.)” details for users willing to give free service of certain kinds.

The application will have “users” who will need service from the certain kind (ex: I need plumbing repair work in my house) and request the same in website.

Now the Admin will see the requests and can assign one of the “Available” service provider for the task.

Help needed to design these tables in sql server so that Admin can see which service provider is free and fix the appointment for the work.

Thanks.


Sponsored



Replies

Kalman Toth on Tue, 19 Feb 2013 13:02:05


Can you post your starter CREATE TABLEs?

>Every day 3:00 pm to 5:00 pm or Sunday 4:00pm to 8:00 pm

It is hard to deal with time ranges like that in SQL. Tentative design.

CREATE TABLE WeekCalendar (
WeekCalendarID SMALLINT IDENTITY(1,1) PRIMARY KEY,
DayOfWeek tinyint not null CHECK (DayOfWeek BETWEEN 1 AND 7),
HourOfDay tinyint not null CHECK (HourOfDay BETWEEN 0 AND 23),
UNIQUE(DayOfWeek, HourOfDay),
ModifiedDate date default CURRENT_TIMESTAMP);
GO

CREATE TABLE UserAvailable (
UserID INT not null REFERENCES Users,
WeekCalendarID smallint not null REFERENCES WeekCalendar,
PRIMARY KEY (UserID, WeekCalendarID),
ModifiedDate date default CURRENT_TIMESTAMP);

Sunday 4:00pm to 8:00 pm would be 4 rows in the UserAvailable table.


Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


Prabhat_IE on Thu, 21 Feb 2013 09:45:04


Hello Kaman, thanks for your input.

Here is my initial scripts for the tables for your review.

-- Store ALL Users including Service Provoders. UserTypeid is used to identify those.
create table Users(
	UserId		int IDENTITY(1, 1) PRIMARY KEY,
	UserName	nvarchar(16) UNIQUE,
	UserEmail	nvarchar(128) UNIQUE,
	UserPassword nvarchar(16),
	IsActive	bit,
	UserTypeId	int,  --1 = Admin User, 2 = Service Provider, 3 = End Users
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--Type of services offered Ex: Plumbing, Electrical etc.
create table ServiceTypes(
	ServiceTypeId	int IDENTITY(1, 1) PRIMARY KEY,
	ServiceTypeName	nvarchar(20) UNIQUE,
	Description		nvarchar(256),
	UpdateTime		datetime default CURRENT_TIMESTAMP)

-- Users can have multiple skills/services those they can offer.
create table ServicesOffered(
	OfferId			int IDENTITY(1, 1) PRIMARY KEY,
	ServiceProviderId int REFERENCES Users(UserId),
	ServiceTypeId	int REFERENCES ServiceTypes(ServiceTypeId),
	UpdateTime		datetime default CURRENT_TIMESTAMP,
	CONSTRAINT UN_ServicesOffered_UserIdServiceTypeId UNIQUE NONCLUSTERED(ServiceProviderId, ServiceTypeId) )

--This table will have manual daily basis availability list.
--PLEASE ADVISE BETTER WAY where we can store as below:
--  "I am available on ALL Days 3:00 pm to 5:00 pm, OR Available on Sat and Sun 3:00 pm to 6:00 pm. ETC.
create table ServiceProviderAvilability(
	AvilabilityId	int IDENTITY(1, 1) PRIMARY KEY,
	ServiceProviderId int REFERENCES Users(UserId),
	IsAvailable	bit DEFAULT 1,		--Manually Updated by Admin
	AvailableFrom	datetime,
	AvailableTill	datetime,
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--All users can request for the Services they need.
create table ServiceRequests(
	ServiceRequestId	int IDENTITY(1, 1) PRIMARY KEY,
	UserId			int REFERENCES Users(UserId),
	ServiceTypeId	int REFERENCES ServiceTypes(ServiceTypeId),
	RequestTitle		nvarchar(50),
	ReuestDescription	nvarchar(1024),
	CreatedTime		datetime default CURRENT_TIMESTAMP,
	RequestStatus	int,	--Will Hold Request Status: ex: 1 = Pending, 2 = Approved, 3 = Completed etc.
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--Admin "Manually" assign the Service Providers to each Service Request.
--IsAvailable Column in the ServiceProviderAvilability Table.
create table ServiceAssignaments(
	AsignmantId		int IDENTITY(1, 1) PRIMARY KEY,
	ServiceRequestId	int REFERENCES ServiceRequests(ServiceRequestId),
	ServiceProviderId	int REFERENCES Users(UserId),
	AvilabilityId		int REFERENCES ServiceProviderAvilability(AvilabilityId),
	UpdateTime		datetime default CURRENT_TIMESTAMP)

Thanks,
Prabhat

Kalman Toth on Thu, 21 Feb 2013 11:46:26


Hi Prabhat,

I made a few minor changes.  The biggest question is the availability calendar. Is it one-time availability, recurring or both? Take a look at SQL Server Agent job schedule page for some ideas.

--1 = Admin User, 2 = Service Provider, 3 = End Users
create table UserType(
	UserTypeId		tinyint IDENTITY(1, 1) PRIMARY KEY,
	Type			nvarchar(32) UNIQUE,
	UpdateTime		datetime default CURRENT_TIMESTAMP);

-- Store ALL Users including Service Provoders. UserTypeid is used to identify those.
create table Users(
	UserId		int IDENTITY(1, 1) PRIMARY KEY,
	UserName	nvarchar(16) UNIQUE,
	UserEmail	nvarchar(128) UNIQUE,
	UserPassword nvarchar(16),
	IsActive	bit,
	UserTypeId	tinyint REFERENCES UserType,  
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--Type of services offered Ex: Plumbing, Electrical etc.
create table ServiceTypes(
	ServiceTypeId	int IDENTITY(1, 1) PRIMARY KEY,
	ServiceTypeName	nvarchar(20) UNIQUE,
	Description		nvarchar(256),
	UpdateTime		datetime default CURRENT_TIMESTAMP)

-- Users can have multiple skills/services those they can offer.
create table ServicesOffered(
	OfferId			int IDENTITY(1, 1) PRIMARY KEY,
	ServiceProviderId int REFERENCES Users(UserId),
	ServiceTypeId	int REFERENCES ServiceTypes(ServiceTypeId),
	UpdateTime		datetime default CURRENT_TIMESTAMP,
	CONSTRAINT UN_ServicesOffered_UserIdServiceTypeId UNIQUE NONCLUSTERED(ServiceProviderId, ServiceTypeId) )

--This table will have manual daily basis availability list.
--PLEASE ADVISE BETTER WAY where we can store as below:
--  "I am available on ALL Days 3:00 pm to 5:00 pm, OR Available on Sat and Sun 3:00 pm to 6:00 pm. ETC.
create table ServiceProviderAvilability(
	AvilabilityId	int IDENTITY(1, 1) PRIMARY KEY,
	ServiceProviderId int REFERENCES Users(UserId),
	IsAvailable	bit DEFAULT 1,		--Manually Updated by Admin
	AvailableFrom	datetime,
	AvailableTill	datetime,
	UpdateTime		datetime default CURRENT_TIMESTAMP,
	UNIQUE (ServiceProviderID, AvailableFROM));

--All users can request for the Services they need.
create table ServiceRequests(
	ServiceRequestId	int IDENTITY(1, 1) PRIMARY KEY,
	UserId			int REFERENCES Users(UserId),
	ServiceTypeId	int REFERENCES ServiceTypes(ServiceTypeId),
	RequestTitle		nvarchar(50),
	ReuestDescription	nvarchar(1024),
	CreatedTime		datetime default CURRENT_TIMESTAMP,
	UNIQUE (UserID, CreatedTime),
	RequestStatus	tinyint REFERENCES RequestStatus,	--Will Hold Request Status: ex: 1 = Pending, 2 = Approved, 3 = Completed etc.
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--Admin "Manually" assign the Service Providers to each Service Request.
--IsAvailable Column in the ServiceProviderAvilability Table.
create table ServiceAssignments(
	AssignmentId		int IDENTITY(1, 1) PRIMARY KEY,
	ServiceRequestId	int REFERENCES ServiceRequests(ServiceRequestId),
	ServiceProviderId	int REFERENCES Users(UserId),
	AvailabilityId		int REFERENCES ServiceProviderAvilability(AvilabilityId),
	UNIQUE(ServiceRequestID, ServiceProviderID, AvailabilityID),
	UpdateTime		datetime default CURRENT_TIMESTAMP)


Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



Prabhat_IE on Thu, 21 Feb 2013 17:26:42


Thanks Kalman for your input.

I do have the "UserType" table but did not included in my script to make that small :).

Reg: The biggest question is the availability calendar. Is it one-time availability, recurring or both?  
Yes I too agree and wanted to know how we can store the availability and the assignment for the service requests. 
And yes it can be one time or recurring too.

Thanks,
Prabhat

davidbaxterbrowne on Thu, 21 Feb 2013 18:18:19


I think that's a reasonable model, but I'd just like to advocate a bit for getting rid of the IDENTITY PK for some of the tables, and using compound natural keys instead.  As you've rightly discovered, adding the synthetic key doesn't prevent you from needing an additional compound unique constraint.  And often that's an signal that you can just ditch the synthetic key and have just one key for the table.  Something like:

--1 = Admin User, 2 = Service Provider, 3 = End Users
create table UserType(
	UserTypeId		tinyint IDENTITY(1, 1) PRIMARY KEY,
	Type			nvarchar(32) UNIQUE,
	UpdateTime		datetime default CURRENT_TIMESTAMP);

-- Store ALL Users including Service Provoders. UserTypeid is used to identify those.
create table Users(
	UserId		int IDENTITY(1, 1) PRIMARY KEY,
	UserName	nvarchar(16) UNIQUE,
	UserEmail	nvarchar(128) UNIQUE,
	UserPassword nvarchar(16),
	IsActive	bit,
	UserTypeId	tinyint REFERENCES UserType,  
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--Type of services offered Ex: Plumbing, Electrical etc.
create table ServiceTypes(
	ServiceTypeId	int IDENTITY(1, 1) PRIMARY KEY,
	ServiceTypeName	nvarchar(20) UNIQUE,
	Description		nvarchar(256),
	UpdateTime		datetime default CURRENT_TIMESTAMP)

-- Users can have multiple skills/services those they can offer.
create table ServicesOffered(
	ServiceProviderId int REFERENCES Users(UserId),
	ServiceTypeId	int REFERENCES ServiceTypes(ServiceTypeId),
	UpdateTime		datetime default CURRENT_TIMESTAMP,
	PRIMARY KEY(ServiceProviderId, ServiceTypeId) )

--This table will have manual daily basis availability list.
--PLEASE ADVISE BETTER WAY where we can store as below:
--  "I am available on ALL Days 3:00 pm to 5:00 pm, OR Available on Sat and Sun 3:00 pm to 6:00 pm. ETC.
create table ServiceProviderAvilability(
	ServiceProviderId int REFERENCES Users(UserId),
	IsAvailable	bit DEFAULT 1,		--Manually Updated by Admin
	AvailableFrom	datetime,
	AvailableTill	datetime,
	UpdateTime		datetime default CURRENT_TIMESTAMP,
	PRIMARY KEY (ServiceProviderID, AvailableFROM));

--All users can request for the Services they need.
create table ServiceRequests(
	ServiceRequestId	int IDENTITY(1, 1) PRIMARY KEY,
	UserId			int REFERENCES Users(UserId),
	ServiceTypeId	int REFERENCES ServiceTypes(ServiceTypeId),
	RequestTitle		nvarchar(50),
	ReuestDescription	nvarchar(1024),
	CreatedTime		datetime default CURRENT_TIMESTAMP,
	UNIQUE (UserID, CreatedTime),
	RequestStatus	tinyint REFERENCES RequestStatus,	--Will Hold Request Status: ex: 1 = Pending, 2 = Approved, 3 = Completed etc.
	UpdateTime		datetime default CURRENT_TIMESTAMP)

--Admin "Manually" assign the Service Providers to each Service Request.
--IsAvailable Column in the ServiceProviderAvilability Table.
create table ServiceAssignments(
	ServiceRequestId	int REFERENCES ServiceRequests(ServiceRequestId),
	ServiceProviderID	int REFERENCES Users(UserId),
	AvailabilityStart datetime,
  CONSTRAINT FK_ServcieAssignments_Availabiliety FOREIGN KEY (ServiceProviderID, AvailabilityStart) REFERENCES ServiceProviderAvilability(ServiceProviderID, AvailableFROM),
	PRIMARY KEY (ServiceRequestID, ServiceProviderID, AvailabilityStart),
	UpdateTime		datetime default CURRENT_TIMESTAMP)

David

Prabhat_IE on Thu, 21 Feb 2013 18:56:29


Thanks David for your input, those are helpful.

I am looking forward for the main part of the question where I should be able to store availability for the Service Provider in either for a single day or multiple times in a single day or multiples days in a week as recurring.

And assigning the service provider for the service requests. I know this will be based on above design changes.

Thanks,
Prabhat

davidbaxterbrowne on Thu, 21 Feb 2013 19:05:22


There may be some configuration data that you use to generate the ServiceProviderAvailability rows, eg working days, capacity etc.  But I would generate the availability slots for each ServiceProvider in a batch job, and then manually edit them (eg if a provider has an unscheduled day off).  That way the user can pick a time window and simply list all the ServiceProviderAvailibility rows to pick a ServiceProvider.

David

Kalman Toth on Fri, 22 Feb 2013 08:30:36


I'd just like to advocate a bit for getting rid of the IDENTITY PK for some of the tables, and using compound natural keys instead

Hi David,

I prefer INT IDENTITY SURROGATE PRIMARY KEYs and placing UNIQUE (KEY) constraint on compound NATURAL KEYs when designing OLTP databases.

The issues with compound PRIMARY KEYs:

1. Require compound FOREIGN KEYs thus adding complexity & duplicating content

2. Require multiple predicates in JOINs thus decreasing developers productivity

3. Slow performance especially with varchar columns in the compound PRIMARY KEY

Exception to the above are junction tables which are not referenced from other tables.


Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012




davidbaxterbrowne on Fri, 22 Feb 2013 23:10:55


1. and 2. are valid points, and your prefered way is works well, but...

1.  You will often have the other key column on your table anyway.  Take ServiceProviderAvilability.  It already had the DerviceProviderID, and I just replaced the ServiceProviderAvilability_ID with AvailableFrom.   For "linking" tables and "child" tables using the foreign key columns as the leading columns in the primary key is both simple and efficient.

2. Yes, although only for people writing TSQL queries directly.  And if it's the right model, then I can live with that.

3.  I think this one is mostly wrong.  Or at least not predictably right enough for it to impact the logical design.  The surrogate key is a completely net-new data structure that has to be stored and maintained.  You still must have the unique index supporting the natural keys.  So any performance gains for would first have to pay for that. 

But note I did leave the surrogate key on ServiceRequests, so I don't really have a hard-and-fast rule here.  I always use compound keys with linking tables, and try to use compound keys with "child tables", but not too hard.

David


David http://blogs.msdn.com/b/dbrowne/



Kalman Toth on Sat, 23 Feb 2013 09:40:14


>I should be able to store availability for the Service Provider in either for a single day or multiple times in a single day or multiples days in a week as recurring.

Use the WeekCalendar table for recurring availability and the ServiceProviderAvilability table for one-time availability.

The availability query should dynamically merge the data in the two tables to produce the availability for a given day.

Prabhat_IE on Sat, 23 Feb 2013 18:09:34


Thanks Kalman for the input. I am still thinking for a better model for those data.

-Prabhat