Pros/Cons of Multivalue Database Columns

I'm just wondering what any pros and cons of using multivalue columns in a database are.

I'm designing a database which will have a column for FABRIC_TYPES_AVAILABLE for a certain FURNITURE_ITEM. Each FURNITURE_ITEM can have multiple FABRIC_TYPES_AVAILABLE of course. So I was just going to store a 2 or 3 digit number of the FABRIC_TYPES_AVAILABLE in that row. So I would have something like...34,24,453,32,23,45,67,65,43,21,21,45.

Anyway...thanks in advance for any information. Links I could read would be great too...b/c I did do a bit of searching, but didnt find much.::I'm just wondering what any pros and cons of using multivalue columns in a database are.

I suggest you read a book about database design. This is pretty well explained in about every into chapter about noirmalization that I have ever seen so far.

Look for (at amazon or in your local Bookstore):

"SQL for Dummies".|||do a look up table. they're faster to query than text based columns|||thona thx.

but i do know its not good for normalization; dont exactly have time to go read a book. hehe.

anyway...im going to go head and go for it, or i will use a lookup table.

thx fellaz|||http://www.sqlservercentral.com/columnists/dsumlin/lookupstrategy_printversion.asp

thats about the best info i could find about lookup tables. could you tell me a little bit more about them?

are they basically, just another table, with ID and Value columns for a certain datatype?

If thats true, i understand that...but then in the table where the values from the lookup table go...do you still store your ID as 23,32,43,54,3,25? I mean...if not...how else would a person know what rows to get from the reference table?

this is the way i was originally doing it; so im assuming there is a different/more correct way to do it, which i'm asking you about.|||you will normalize the database now, or you will normalize it in the future. If you don't exactly have the time to read a book, and you don't know how to normalize a database, I question your strength as a developer. I read constantly, and I always make room to read about something. Even if it's in the restroom. (yeah, I got tech mags in there. :))|||You will have three tables:
1. Furniture_Item
Furniture_ItemID
Furnite_Item (description, etc)
Blah blah (but no fabric type info on this table)

Your lookup table:
2.Fabric_Type
Fabric_TypeID
Fabric_Type (the description)

A link table because you have a many to many relationship between your Furniture_Items and Fabric_Type.
3.Item_FabricType
Item_FabricTypeID
Furniture_ItemID
Fabric_TypeID

The third table contains all the FabricTypes per Item,: so you have no FabricTypes on your Furniture_Item table.

I also suggest you do a bit of reading about database design; it's for your own good. :)

HTH

Property Database Design Issue

Hi !!

I need some inputs for designing Property Database

Property Types:

Condo/Townhouse

Single Family Home

Multi Family Home

Rental

Property Features

Sq Ft

# Of floors

New Construction

Carpeted Floors

Hardwood Floors

Living Room

Formal Dining Room

# of Bedrooms

# of Bathrooms

Den/Office

Water : City or Private

Sewer: City or Septic

Family Roomand more

Lot Features

Mountain View

Golf Course Lot

Golf Course Viewand More

Community Features

Boating

Golf

Gym

Swimmingand More

Here is what I am thinking:

1) I probably don't need any table for Property Type as I can hard code them into Drop Down List

2) Water and Sewer feature can de coded into Drop Down List too

3) How rest of the table design should work?

Table called Property, Lot Feature, Community Feature, Property_Lot, Property_Community

Property Table has PropertyID (P1, P2, P3) and other Info

Lot Feature has Lot Feature ID (L1, L2, L3) and

Community Feature has (C1, C2, C3)

Property can have zero or More Lot Feature

soProperty_Lot table will have:

P1, L1

P1, L2

P1, L3

P2, L3

P3, L1

Property can have zero or more Community Feature, soProperty_Community

P1, C1

P2, C1

P2, C2

P3, C3

Is this right?

How do we design search query?

I think query will be dynamic query? correct?

Thanks !!!!

1) If you making them a radio button, ok. If you are putting it into a dropdown, I'd make a table entry.

2) See #1.

3) Looks pretty good, but when you say P1,P2,L1,L2, just make the ID's an autoincrementing identity, not 'P1' perse, but an identity of 1,2,3,etc. I'll assume that is what you meant.

Yes, that is right.

Seach for what?

I wouldn't use a dynamic query, no.

|||

Looked into your suggestions.

Search for:

Give me a list of property where Property Has feature L1 AND L2 and C1

or

Give me a list of property where Property has feature L1

Now, this type of query will need a join with other tables and parameters are completely unknown, # of parameters selected and passed to query are unknown till runtime. In this case what is the alternative for dynamic query?

--------------------

We also thought about something like this:

1) Property Table

2)Property_Lot table. This table looks like this:

0 = No

1 = Yes

Columns are:

PropertyID, MountainView, Golf View, Cornor Lot, River front Lot (and about 10 more)

Data:

P1, 0, 0, 1, 1

P2, 1, 1, 1, 0

This means Property P1 is Cornor Lot and River front

P2 is Mountain View, Golf View, Cornor Lot

Now, the problem is how to perform query on this? We might have to pass all parameter in particular order?

---- OR -----

Should I create column in Property Table "Lot Info", "Community Info"

and save comma seperated value in that column

PropertyID Lot Features Community Features

P1 L1, L2, L3 C1, C3

P2 L3 C2, C3

and now use SQL Server Full Text Search when some one says "Give me a list on Properties with Lot Feature L3 and Community Feature C3"

or "Give me a list of Properties with Lot Feature L1"

Any suggestion?

|||

You first design is best (The one in the original message).

Yes, you would do a join. Sorry, you are probably best just doing a dynamic query. You can do a static one, but it'd be extremely large, and probably pretty inefficient.

A simple dynamic query using your original design would be like:

SELECT *
FROM Properties p
JOIN PropertyLot pl ON (p.PropertyId=pl.PropertyId AND pl.LotFeatureID={Feature 1})
JOIN PropertyLot pl2 ON (p.PropertyId=pl2.PropertyId AND pl2.LotFeatureID={Feature 2})

That is assuming that you have the feature id's already, if you don't then you can do this:

SELECT *
FROM Properties p
JOIN PropertyLot pl ON (p.PropertyId=pl.PropertyId)
JOIN LotFeatures lf1 ON (pl.LotFeatureID=lf1.LotFeatureID AND lf1.Description={Feature 1})
JOIN PropertyLot pl2 ON (p.PropertyId=pl2.PropertyId)
JOIN LotFeatures lf2 ON (pl.LotFeatureID=lf2.LotFeatureID AND lf2.Description={Feature 2})

etc.

You can do a static one like this:

SELECT *
FROM Properties p
WHERE (SELECT COUNT(*) FROM PropertyLot pl JOIN Split(@.LotFeatures) s ON (pl.LotFeatureID=s.ID AND p.id=pl.id))=ValueCount(@.LotFeatures)

That assumes that you have a comma-delimited list of LotFeatureID's in @.LotFeatures, and that you have already written a Split function that takes a comma-delimited varchar field and returns a table with a single column named ID that has each value in a new row. ValueCount is a function (You have to write it) that accepts a comma-delimited varchar and returns the number of values in it. I assume that a null varchar is 0, and an empty string is 0 for count, and both return no rows from split.

|||How about using Full Text Search on 2nd approach i.e. Comma Seperated list for LotFeatures and CommunityFeatures?