|
I would suggest to accomplish this by using relational tables.
Have a table with the business info, and then give the business an Id number.
Create a table that has BusinessId,Day, TimeWorkStart, TimeWorkEnd, TimeLunchStart, TimeLunchEnd (or whatever you want to call them).
The BusinessID would be the foreign key that would refer to the Business info table. To get the schedule, you would need to join up on the BusinessId of both tables.
The way you listed above could be one approach (and I've seen similar things done in program), but you might come to a point where the design restricts you from adding additional schedules without storing a bunch of duplicate data.
Also less data is being stored. If your table has 28 columns, but only has, say Monday and Tuesday workdays, then there are 20 unused columns.
|
|
|
|
|
Thanks a lot for your help! I really appreciate it.
I liked your idea, but once I started to implement it, I've come up with a "joined" solution, that combines the advantages of yours and mine approaches. Here is the idea.
Although, there is a big variety of businesses in my city, many of them share the same schedule. Therefore, I think it would be better to create a table "Schedules", which will contain the list of all schedules, that the businesses use. This way, each of 24/7 shops in my database will contain only an ID for appropriate 24/7 schedule, therefore, I will avoid lots of duplication. And, if I meet some odd schedule, I can just add it into the table with the schedules.
If you find any faults in this idea, or have some suggestions - I'll appreciate that.
Once again, thanks for time&effort
|
|
|
|
|
Something that might happen in the future is that a business might need to have 2 types of schedules, causing duplicate data in the business table on every column but ScheduleId.
You could "map" BusinessId's to SchedulesId's in a view, so you might have tables like:
BusinessID, Name, ...
ScheduleId, Day, StartTime, EndTime
Mapping Table: BusinessId, ScheduleId (with a unique index on BusinessId, ScheduleID so you don't dupe data)
Then have a view
Select ...<br />
From MappingTable m<br />
Join ...
But if you have a business rule similar to one business can have one schedule per day, then I think your Joined solution will work fine.
|
|
|
|
|
I have a doubt on Foreign key column. I understand that a Foregin key column can hold NULL values (meaning it need not be required always), but can it hold invalid data in it?
That is, it is holding the ROW_ID of another table where that record is not available. Will it throw an error or it will accept the invalid entry?
|
|
|
|
|
Foreign key is used to ensure data integrity. If you want to allow invalid entries, I cannot see the point in using foreign key.
|
|
|
|
|
Very new to SQL and I learn by example but cannot find anything like this after tons of searching.
I have an Excel workbook of software application names and versions. Many have the same 'root' name but different version number. e.g. root = "McAfee VirusScan"
sheet columns...
APP NAME -- VERSION # -- MANUF -- INSTALL COUNT
I think a View might be best(?) but no idea how to code it.
The View needs to SUM the INSTALL COUNTs for all similar APP NAMEs & VERSIONs(similar determined by using first few characters or something - options here?)
example:
McAfee VirusScan v1.01 21259
McAfee VirusScan v3.20 14234
... There are thousands of APPs. I will not know all the possible versions.
Anyone do this before and have some example code or could help?
Thx in advance!
|
|
|
|
|
How can I understand you your query should be something like this:
Select sum(Installcount)<br />
from Tablename<br />
group by version
If this don't help you then post your data how is stored on table and post result which you want to get.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Select AppName, Version, Sum(InstallCount)
From table
Group By AppName, Version
OrderBy AppName
|
|
|
|
|
Hello sir I am Convert Access Databse to sql server 2000 please tell me how to convert it
saurabh bhardwaj
|
|
|
|
|
Hi Saurabh,
Please follow these link. That may be helpfull.
Link 1[^]
Link 2[^]
Regards
|
|
|
|
|
|
I am using Rank() function in my VIEW.
it working in sql2005 but not in sql2000.
Is there any alternative function for Rank() or Row_Number() in sql server 2000?
|
|
|
|
|
No
You can simulate it using a cursor and identity field
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Could you give a sample example for the same?
|
|
|
|
|
Sorry mate you are going to have to do the work yourself, I am willing point the way not do the work. Besides I use 2005 why would i code for 2000.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hey try this
Select IDENTITY(int,1,1) AS [Row Number], FieldName
INTO #Temp
from EMPtable
order by
FieldName DESC
SELECT *
FROM #Temp
WHERE [Row Number] = 10
hope this will help you.
Reasons are not Important but Results are Important.
Swati
|
|
|
|
|
I have a .sql file with sql statements to create tables and procedures likewise,
Now we have to use DB2(client insists) as the database,
Is there any tools or anything can convert SQLSERVER2k sql statements to DB2's?
Any help would appreciate!
|
|
|
|
|
you can use import statement,this means you can run .sql file and then and you must import .mdf file
|
|
|
|
|
I am getting the given below error when I am executing VIEW in sql server 2000.
Msg 195, Level 15, State 10, Procedure UVWCUSTOMERNAME_R171, Line 3
'RANK' is not a recognized function name.
Msg 170, Level 15, State 1, Procedure UVWCUSTOMERNAME_R171, Line 5
Line 5: Incorrect syntax near '('.
|
|
|
|
|
It is available only in sql server 2005
|
|
|
|
|
Is there any alternate way to do in sql2000?
|
|
|
|
|
I have a database "DB_01" whose table are in read only mode. I have another database "DB_02" which at present does not have any tables. I want to copy all tables along with their structure from DB_01 into DB_02. Please suggest SQL query or another mechanism by which this can be achieved.
Thanks in advance.
Ankit Jain
|
|
|
|
|
you can script out the tables, views and procs using management studio.
Use the Red-Gate sqlcompare to generate the script.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have created database in SQL server 20005 which I have detached and kept at a secured storage place. Now after formatting my system, and fresh installation of sql server 2005, when I am attaching this database table, it is adding in read only mode. Please suggest possible method to make it full accessible for all kind of operations.
Thanks in advance,
Ankit Jain
|
|
|
|
|
I have a .NET app I've written in VS2005. On a previous project I used MySQL, and managed to put together a silent install/config bootstrapper together. The installer was made using the Bootstrap Manifest Generator[^], which has worked well for me in the past. The config part was just a .exe I wrote that ran the MySQLConfig.exe with the appropriate arguments and started the service.
On my current project, we'd like to use PostgreSQL (the licensing works out better here). However, I can't get the bootstrapper to install it for me with a test app at all. It says "Installing PostgreSQL" then moves on to install my app, but PG never gets installed. I'm trying to install using postgresql-8.3.msi as my main file, and I've added the following files as "additional" files to my bootstrap package:
postgresql-8.3-int.msi
SETUP.bat
UPGRADE.bat
vcredist_x86.exe.
The only files I would think that I would need is postgresql-8.3-int.msi and postgresql-8.3.msi. Any ideas why it won't install? Is there something I'm missing here?
Thanks,
Dybs
|
|
|
|