|
Good afternoon. I am working on an automated booking system, and I am trying to figure a strategy for resource availability as it pertains to data storage.
The database has service providers in it (People who do things) and they are each available up to 24 hours a day 7 days a week. They might be available only on Monday from 1 to 2 AM. They need to have the ability to say they are available in different hour brackets.
I am scratching my head a little bit thinking about the database design. I could have 169 columns in the table (1 for userid, and then 1 for each hour of the week), but that sounds laborious. I also considered a binary style string 168 characters long where each hour of the week has a specific string position (1000000100000111110000), but that is equally or more laborious. I also considered the idea of allowing the user to store their information in a table that tracks the day of the week, and the start/end time of their availability. The table could have multiple rows per day so if the user is available from 1 to 5 and then 6 to 10, he or she could have two Monday rows in the DB, but I am not sold on this strategy either.
Anyways, does anyone have experience in this particular kind of data storage that they would like to share? Any and all thoughts are welcome.
Cheers
|
|
|
|
|
You may want to consider 7 columns with a 24 byte array in each column. A blank indicated availability. My reason for this is that is would be easy to generate queries like "who is available on Mondays ?".
select username from availTable where mondayAvail = ""
Your design really depends on how you think you will be using (querying) the data.
You may want to consider the ability to store a person's availability for a given time frame. Consider ... Joe is available Monday (9:00 - 17:00) for January, but after that he will switch to evenings (17:00 - 02:00). With that requirement, then I would go with an effective data in the front of the availability table which indicates FromThisDate ToThisDate on these days of week and these hours, the resource is available.
Sorry I couldn't be more definitive.
Good luck.
|
|
|
|
|
Quite insightful, thank you. In regards to the 24 bit array, when you say that a blank indicates availability how would you manage partial availability? Were you thinking the same sort of array as I suggested prior? (111100001111111111111111 indicates availability at all times aside from 5 to 9 AM)
|
|
|
|
|
Well I would say you can take your bits and .....
I would not even consider the bit type solution, purely from a downstream support aspect. Imagine coming in to support such a system.
I think the table solution is going to be more flexible and supportable and will be more queriable by your users (who probably have no idea what a bit is). I would have 2 tables, Dates and Times, allow the user to enter as many times for a day as they require, supply a propagate forward by week/month methods. This can then interact with a calendar control.
|
|
|
|
|
Hi,When I am Installing SQL Server 2008 advance series,at last I am getting one exception and stopping the installation.
The Error is : Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
I have tried in manyways,but not able to fix.Appreaciate your valuable advise on this Inadvance.
the following msg is the Summary Log file.you can go through it once.
Thanks,
Srikrishna
Overall summary:
Final result: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
Exit code (Decimal): -2068643839
Exit facility code: 1203
Exit error code: 1
Exit message: SQL Server installation failed. To continue, investigate the reason for the failure, correct the problem, uninstall SQL Server, and then rerun SQL Server Setup.
Start time: 2009-11-10 17:47:51
End time: 2009-11-10 18:29:49
Requested action: Install
Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.0.1600.22%26EvtType%3d0x429FDFA3%400x0A2FBD17%401211%401
Machine Properties:
Machine name: SRIKRISHNA-PC
Machine processor count: 2
OS version: Windows Vista
OS service pack:
OS region: India
OS language: English (United States)
OS architecture: x86
Process architecture: 32 Bit
OS clustered: No
Product features discovered:
Product Instance Instance ID Feature Language Edition Version Clustered
Package properties:
Description: SQL Server Database Services 2008
SQLProductFamilyCode: {628F8F38-600E-493D-9946-F4178F20A8A9}
ProductName: SQL2008
Type: RTM
Version: 10
SPLevel: 0
Installation location: c:\c9d261aac71184b75483\x86\setup\
Installation edition: EXPRESS_ADVANCED
User Input Settings:
ACTION: Install
ADDCURRENTUSERASSQLADMIN: False
AGTSVCACCOUNT: NT AUTHORITY\NETWORK SERVICE
AGTSVCPASSWORD: *****
AGTSVCSTARTUPTYPE: Disabled
ASBACKUPDIR: Backup
ASCOLLATION: Latin1_General_CI_AS
ASCONFIGDIR: Config
ASDATADIR: Data
ASDOMAINGROUP: <empty>
ASLOGDIR: Log
ASPROVIDERMSOLAP: 1
ASSVCACCOUNT: <empty>
ASSVCPASSWORD: *****
ASSVCSTARTUPTYPE: Automatic
ASSYSADMINACCOUNTS: <empty>
ASTEMPDIR: Temp
BROWSERSVCSTARTUPTYPE: Automatic
CONFIGURATIONFILE: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\ConfigurationFile.ini
ENABLERANU: True
ERRORREPORTING: False
FEATURES: SQLENGINE,REPLICATION,FULLTEXT,RS,BIDS,SSMS,SNAC_SDK,OCS
FILESTREAMLEVEL: 3
FILESTREAMSHARENAME: SQLEXPRESS2008
FTSVCACCOUNT: NT AUTHORITY\LOCAL SERVICE
FTSVCPASSWORD: *****
HELP: False
INDICATEPROGRESS: False
INSTALLSHAREDDIR: C:\Program Files\Microsoft SQL Server\
INSTALLSHAREDWOWDIR: C:\Program Files\Microsoft SQL Server\
INSTALLSQLDATADIR: <empty>
INSTANCEDIR: C:\Program Files\Microsoft SQL Server\
INSTANCEID: SQLExpress2008
INSTANCENAME: SQLEXPRESS2008
ISSVCACCOUNT: NT AUTHORITY\NetworkService
ISSVCPASSWORD: *****
ISSVCSTARTUPTYPE: Automatic
MEDIASOURCE: c:\c9d261aac71184b75483\
NPENABLED: 0
PID: *****
QUIET: False
QUIETSIMPLE: False
RSINSTALLMODE: DefaultNativeMode
RSSVCACCOUNT: NT AUTHORITY\LOCAL SERVICE
RSSVCPASSWORD: *****
RSSVCSTARTUPTYPE: Automatic
SAPWD: *****
SECURITYMODE: <empty>
SQLBACKUPDIR: <empty>
SQLCOLLATION: Latin1_General_CI_AI
SQLSVCACCOUNT: NT AUTHORITY\NETWORK SERVICE
SQLSVCPASSWORD: *****
SQLSVCSTARTUPTYPE: Automatic
SQLSYSADMINACCOUNTS: MINVESTAHYD\srikrishna.murthy
SQLTEMPDBDIR: <empty>
SQLTEMPDBLOGDIR: <empty>
SQLUSERDBDIR: <empty>
SQLUSERDBLOGDIR: <empty>
SQMREPORTING: False
TCPENABLED: 0
X86: False
Configuration file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\ConfigurationFile.ini
Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: SQL Client Connectivity SDK
Status: Passed
MSI status: Passed
Configuration status: Passed
Feature: SQL Server Replication
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Full-Text Search
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Reporting Services
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Management Tools - Basic
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Business Intelligence Development Studio
Status: Failed: see logs for details
MSI status: Passed
Configuration status: Failed: see details below
Configuration error code: 0x0A2FBD17@1211@1
Configuration error description: A MOF syntax error occurred.
Configuration log: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\Detail.txt
Feature: Microsoft Sync Framework
Status: Passed
MSI status: Passed
Configuration status: Passed
Rules with failures:
Global rules:
Scenario specific rules:
Rules report file: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20091110_174403\SystemConfigurationCheck_Report.htm
|
|
|
|
|
It is possible that you do not have the WMI service running. To activate the WMI service, go to 'add or remove programs' and then into turn on/off windows features.
Alternatvely there are issues if you have not cleanly uninstalled and earlier version. Mark Michaelis' Weblog[^] has an interesting article on this that may help sole your problem.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
I m working in c# .net application with sql server 2005...I have requirement of 1:N search from a record of over 1,00,000 of entries. How can I optimize search.Please suggest me .
|
|
|
|
|
The most efficient search mechanism in SQL is to use a fulltext index.
There's loads of stuff a google[^] search can give you on using a full text index.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
Make sure your table is properly indexed.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
IMO, 100000 rows is definitely not a 'heavy database'. If you properly index your tables, search should be very fast.
|
|
|
|
|
Hi Gents,
I have used two select queries in a stored procedure.. how can i retrieve the values from the two queries in a single datareader
for ex,
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[SearchDoctor]
@fname varchar(50),
@lname varchar(50)
As
Select * from docregistration where fname=@fname and lname=@lname
Declare @Speciality varchar(50)
Select @Speciality=Speciality from docregistration where fname=@fname and lname=@lname
Select Speciality from Speciality where SpecialityID=@Speciality
GO
Thanks a lot in advance
Azeem,
Senior Dotnet Developer
|
|
|
|
|
You can't.
A DataReader itterates through a set of rows from a single result table.
What you need to use is a DataAdaptor with a dataset. Eg.
System.Data.DataSet myDS = new System.Data.DataSet();
System.Data.SqlClient.SqlDataAdapter myAdaptor
= new System.Data.SqlClient.SqlDataAdapter("Execute myProcedure;"
,new System.Data.SqlClient.SqlConnection(myConnectionString));
myAdaptor.Fill(myDS)
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
The Man from U.N.C.L.E. wrote: You can't.
Beg to differ. Dataset.Nextresult does it (may have syntax slightly wrong, its not strictly a sql question and I don't have visual studio on this machine)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Good point. I have forgotten that method (on the DataReader actualy), as I tend to use DataReaders for single recordsets and dataadaptor.Fill for multiple record sets.
If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
www.JacksonSoft.co.uk
|
|
|
|
|
So do I as a rule, but I remembered it from before ADO.NET, when theree were no such things as datasets. Also useful as datareaders are faster than datasets.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Execute DataReader.NextResult() should do the trick.
Wout Louwers
|
|
|
|
|
I want to know about in physical database design in overview.If one of u has some links about this, pls give me.
|
|
|
|
|
|
Physical database design means what exactly you want to do...?
|
|
|
|
|
How can i iterate each rows for my result set in a sql server stored procedure.
My intention is to call a stored procedure in different databases and combine the result. Like
GetDatabases()
Foreach database -- I am here now
CallStoredProcedure()
StoreTheResultInTempDB()
Return the resultset
My small attempt...
|
|
|
|
|
Look into cursors [^]- they are evil but necessary for this type of processing
First hit looks promising
Alternative use a While loop[^]
|
|
|
|
|
Give a try with this... I made this based on my understanding
Declare @GetDBNames sysname
Declare @DynSql nvarchar(max)
declare @result table ([Database_Name] nvarchar(128), [Stored Procedure Name] sysname)
Declare DBNames cursor for
Select '['+name+']' from master.dbo.sysdatabases
open DBNames
FETCH NEXT FROM DBNames into @GetDBNames
WHILE @@FETCH_STATUS=0
BEGIN
SET @DynSql = '
Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name''
From '+ @GetDBNames+'.Information_Schema.Routines '
insert @result exec sp_executesql @DynSql
FETCH NEXT FROM DBNames into @GetDBNames
END
Close DBNames
Deallocate DBNames
select * from @result
It gives me all the stored procs names pertaining to the databases.
Hope this helps.
Niladri Biswas
|
|
|
|
|
Hello , I want to cut out all space in my text "Where are you now?", so i want this"Whereareyounow?"
Can any want help me (SQL)?
|
|
|
|
|
Try this
declare @str as varchar(100)
set @str = 'Where are you now?' -- original data
select REPLACE(@str,' ' ,'') as RemoveSpace
Output:
RemoveSpace
Whereareyounow?
This will even work for
'Where are you now?'
Let me know in case of any concern.
Niladri Biswas
modified on Monday, November 9, 2009 11:42 PM
|
|
|
|
|
now i reach my result..thanks alot
|
|
|
|