I have one Table including 100 Cols,

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

I have one Table including 100 Cols,

Author

Topic

fengvan
Starting Member

7 Posts

Posted - 2002-03-21 : 23:21:05

is this ok? how many Cols in one table will be normal.

i design an online booking system,

customers will choose check_in date and check_out date.

i have to check the availably room and return the average price.
the room includes "contract price", "sale price", "availability".
the price and availability every day is different.

so i create these tables

1)room infor
2)January
3)February
.
.
.
13)December
14)customer order

in every month, there 30 days * (2_price+1_availability)= 90

so i have more then 90 Cols in each month table.

is my design right? please give me some ideas!

thank you!

Edited by - fengvan on 03/21/2002 23:55:26

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-21 : 23:30:15

That all depends on what kind of data you're putting into your table. If you can post the actual table design with some more detail on what you're storing we can better determine if it's OK or should be changed.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-21 : 23:36:40

From experience I have never had 100 columns in my database tables, but in your case it might be necessary but highly unlikely.

Try normalizing (1NF, 2NF, 3NF, 4NF..., and BCNF) your tables. http://www.sqlteam.com/FilterTopics.asp?TopicID=129 has links to a bunch of articles that should give you help on how/why to normalize your tables.

Here is a quick example.
For example you are using a database to store information about books.
Instead of having one table that looks like

Books
----------
BookID
AuthorFirstName
AuthorLastName
AuthorBirthdate
PublisherName
PublisherAddress1
PublisherAddress2
PublisherCity
PublisherState
PublisherZip
BookTitle
BookPageNumbers
BookPrice

you are better off using foreign keys like so.

Books
-------
BookID
AuthorID
PublisherID
Title
Pages
Price

Authors
--------------
AuthorID
FirstName
LastName
Birthdate

Publishers
------------
PublisherID
Name
Address1
Address2
City
State
Zip

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 06:18:40

You should probably have a table something like this:-

Availability
============
Room
Date
Availability
Contract Price
Sale Price

This would be keyed on Room and Date. Example data:-


Room	Date		Availability	Contract_Price	Sale_Price
1	1/1/2002	Y		50		40
1	2/1/2002	Y		50		40
1	3/1/2002	N		60		60
etc.

Note that this would mean lots of rows (1 per room per day) but that is OK

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Edited by - davidpardoe on 03/22/2002 06:19:16

Teroman
Posting Yak Master

115 Posts

Posted - 2002-03-22 : 06:45:19

Got to totally agree with davidpardoe on this one.

Had a friend who had a similar project at uni who also wanted to have a column for each day. I managed to talk him out of it.

Thing is its incredibly nasty to query to find out if its free on a certain day. Plus what happens next year?

Do you add another load of column? And then rewrite all your queries.

Or do you reuse the existing columns...

Eugh, I hate to think about it.

Definitley a row per day.

col

fengvan
Starting Member

7 Posts

Posted - 2002-03-22 : 14:25:41

quote:

You should probably have a table something like this:-

Availability
============
Room
Date
Availability
Contract Price
Sale Price

This would be keyed on Room and Date. Example data:-

Room Date Availability Contract_Price Sale_Price
1 1/1/2002 Y 50 40
1 2/1/2002 Y 50 40
1 3/1/2002 N 60 60
etc.

Note that this would mean lots of rows (1 per room per day) but that is OK

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Edited by - davidpardoe on 03/22/2002 06:19:16

Thank you for your advice.
do you thank i should create 12 tables for 12 months
or simply create one table for the whole year.

can you give me some ideas, how i can organize my queries
for such like :
find out from 02/18/2002 to 03/05/2002 availability and get
the average day price.

we have about 500 - 1000 hotels(about 1500 - 3000 rooms) need search.
365*3000=1095000 (only one year, if two or three years?)
so the table will hold about more than one million records.
do this will affect the queries speed?

thank you

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-22 : 18:49:15

quote:
do you thank i should create 12 tables for 12 months
or simply create one table for the whole year.

NO... One table for ALL DATES FOREVER (you can purge or archive history as appropriate)! If you start messing around with multiple tables that are identical except for one attribute like time, then any future queries will be horribly complex (and everything dynamic) to determine which TABLE it needs to query. This will really slow things down.

SQL Server can store millions and millions of rows in a table and still perform reasonably (with proper indexes and tuning). You also probably don't need a record for every day in existence, but perhaps just for days where reservations occur. (By the way, are rooms always checked out for an entire day, and not just reserved for hours, like a conference room?) You might want to read up on the solutions to the Recruiters Question to see how you can enter only dates with data and still find out about the other dates. This may or may not be the fastest, so some testing is in order. But definitely avoid building multiple tables that are identical in structure and basic function.

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 03/22/2002 18:50:23

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-23 : 17:38:10

I will post back more advice after the weekend!

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-25 : 05:45:14

Use the following tables:-

Availability
============
HotelID
RoomID
Date
Availability
ContractPrice
SalePrice

Hotel
=========
HotelID
HotelName
Area
etc.

Your query for availability would then look like:-

SELECT Hotel.HotelID,Hotel.HotelName,RoomID
FROM Availability
INNER JOIN Hotel ON Availability.HotelID=Hotel.HotelID
WHERE Hotel.Area = @Area AND
Date IS BETWEEN @FirstDate AND @LastDate AND
AVAILABILITY = 'Y'

@Area,@FirstDate and @LastDate are the parameters passed to the query.
This would return all rooms available in the area between the dates suggested - available on all days (You should probably ORDER BY HotelID)

To return the average price you would need to add a GROUP BY on the above query. Note this will return all available rooms as well so fulfills both your requirements.

SELECT Hotel.HotelID,Hotel.HotelName,RoomID,AVG(ContractPrice),AVG(SalePrice)
FROM Availability
INNER JOIN Hotel ON Availability.HotelID=Hotel.HotelID
WHERE Hotel.Area = @Area AND
Date IS BETWEEN @FirstDate AND @LastDate AND
AVAILABILITY = 'Y'
GROUP BY Hotel.HotelID,Hotel.HotelName,RoomID

As for response time - if you create appropriate indexes then a query like this on over a million records will be very quick - a few seconds at the most (obviously dependent on hardware).

Use the index tuning wizard to help you figure out which indexes you need.

Let me know if you want any more help.

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources