Table design for Searching ..  
Author Message
locatao





PostPosted: Sat Nov 22 08:17:26 CST 2003 Top

SQL Server Developer >> Table design for Searching ..

Hiya.
I need to construct a table of Items.

The data will never change.

Which of the two tables is the better design for searching for a keyword
like water, fish, or crater?

Each has 3 columns

1:

1 Item1 box, house, mail, grass, park, water
2 Item2 water, fish, shark, shark, moss, marina
3 Item3 moon, crater, cold, dust, dark, space


2:

1 Item1 box
2 Item1 house
3 Item1 mail
4 Item1 grass
5 Item1 park
6 Item1 water
7 Item2 water
8 Item2 fish
9 Item2 shark

10 Item2 moss

11 Item2 marina
12 Item3 moon
13 Item3 crater

14 Item3 cold

15 Item3 dust

16 Item3 dark

17 Item3 space

SQL Server159  
 
 
Ray





PostPosted: Sat Nov 22 08:17:26 CST 2003 Top

SQL Server Developer >> Table design for Searching .. The second scenario should allow you to utilize an index better and fit
better on your data pages. In scenarios like this, you should test with "set
statistics IO on" and create both scenarios and see which one gives you
lower logical IO.

HTH
--
Ray Higdon MCSE, MCDBA, CCNA
---


> Hiya.
> I need to construct a table of Items.
>
> The data will never change.
>
> Which of the two tables is the better design for searching for a keyword
> like water, fish, or crater?
>
> Each has 3 columns
>
> 1:
>
> 1 Item1 box, house, mail, grass, park, water
> 2 Item2 water, fish, shark, shark, moss, marina
> 3 Item3 moon, crater, cold, dust, dark, space
>
>
> 2:
>
> 1 Item1 box
> 2 Item1 house
> 3 Item1 mail
> 4 Item1 grass
> 5 Item1 park
> 6 Item1 water
> 7 Item2 water
> 8 Item2 fish
> 9 Item2 shark
>
> 10 Item2 moss
>
> 11 Item2 marina
> 12 Item3 moon
> 13 Item3 crater
>
> 14 Item3 cold
>
> 15 Item3 dust
>
> 16 Item3 dark
>
> 17 Item3 space
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


 
 
Vishal





PostPosted: Sat Nov 22 11:07:18 CST 2003 Top

SQL Server Developer >> Table design for Searching .. Jeff,

> The data will never change.

The first table design that you've shown is not a normalized table design. Never store
comma seperate values in the tables. Your temporary lookout may be just "searching
specific values" but always think from the flexibility that has been offered by the table
design.

There are always a few questions that you need to ask yourself. What if tomorrow business
adds one more item as "watermelon" then you will have to write a messy t-sql code to do
differentiate between water and watermelon. (This is just a one example, there could be
many more quandaries like this.)

Your table design needs to be as flexible as possible so that if any kind of requirement
comes in future then it should be possible with little change in the code/query.

Your second table design looks ok, however i would have 2 tables as follows.

item
(itemid int pk,
item_name varchar(15),
item_description varchar(50))

item_details
(detailid int pk,
itemid references item(itemid),
items varchar(15))

so your first table will store values as

1, item1, description for item1
2, item2, description for item2

and second table will hold values as

1,1,box
2,1,house
3,1,mail

and so on...



--
-Vishal


 
 
Jeff





PostPosted: Sat Nov 22 14:13:24 CST 2003 Top

SQL Server Developer >> Table design for Searching .. gotacha that is good. thank you very much for your time.




> Jeff,
>
> > The data will never change.
>
> The first table design that you've shown is not a normalized table design.
Never store
> comma seperate values in the tables. Your temporary lookout may be just
"searching
> specific values" but always think from the flexibility that has been
offered by the table
> design.
>
> There are always a few questions that you need to ask yourself. What if
tomorrow business
> adds one more item as "watermelon" then you will have to write a messy
t-sql code to do
> differentiate between water and watermelon. (This is just a one example,
there could be
> many more quandaries like this.)
>
> Your table design needs to be as flexible as possible so that if any kind
of requirement
> comes in future then it should be possible with little change in the
code/query.
>
> Your second table design looks ok, however i would have 2 tables as
follows.
>
> item
> (itemid int pk,
> item_name varchar(15),
> item_description varchar(50))
>
> item_details
> (detailid int pk,
> itemid references item(itemid),
> items varchar(15))
>
> so your first table will store values as
>
> 1, item1, description for item1
> 2, item2, description for item2
>
> and second table will hold values as
>
> 1,1,box
> 2,1,house
> 3,1,mail
>
> and so on...
>
>
>
> --
> -Vishal
>
>