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.
| Author |
Topic |
|
prowla2006
Starting Member
3 Posts |
 Posted - 2006-12-11 : 19:57:51
|
| I have a Product table with several columns and an auto increment identity id as the primary key...if the ProductDescription is say a varchar(8000) field, should this be held in a separate (with foreign key) Product Description table?what about if ProductDescription is a 'text' field... does the above answer change?or if its a 'varchar(max)' field... does the above answer change?The same question for a gif image (which we do need to store in the database) should this be in a ProductImage table?Thanks.Paul |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2006-12-11 : 20:22:54
|
| Paul, You'll probably get differing opinions on this one, but I'll put forward mine:- a varchar(8000) would only be suitable if you have few other fields in the table. You're only allowed 8060 bytes per row so you might run into problems if you use the column to capacity.- I would tend to use a text field in this circumstance. Text columns are stored separately anyway, so would in effect be similar to using a separate table. At least using a text column you don't have to worry if you occasionally go over the 8k mark.As for the image, there are also differing opinions (mainly the pros/cons of storing images in the database in the first place). These are held in a similar way to text columns, so you can store them in the same table. btw - the max length for a varchar is 8000 anyway.HTH,Tim |
 |
|
|
prowla2006
Starting Member
3 Posts |
Posted - 2006-12-11 : 20:53:15
|
| timmy, thanks for the reply...varchar(max) is (as i understand it) the SQL 2005 replacement for the text field.. i have previously thought it better to put an image field in a separate table but had previously put description fields in the same table... i'm not exactly sure on the size of the description right now... i could limit it at 4000 or 8000 (or 7600 if I'm worried about the rest of the row size) or go for the text optionso the current suggestion is if i go the sql 2005 route, i would have a single product table with varchar(max) for description and varbinary(max) for the product image if its the sql 2000 route.. again a single table but i use text and image fields... (NB. i do need to store the image in the database. but in the application logic i will probably retrieve from the database first time only and subsequently retrieve from hard disk)any other opinions would be very welcome!!as i understand it i wont beable to do use LIKE in the where clause on the description field if its 'text'are there any other "down sides" of using textthanksPaul |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2006-12-12 : 19:03:45
|
| For the image, it sort of depends on the cadinality of the image. If a product has one and only one unique image then it might make sense to put in the same table. However, if you have 10,000 products and a set of (for example sake) 500 images. Then, it would probably make sense to link to the images rather than storing duplicate images.Hopefully, that makes sense. :)-Ryan |
|
|
|
|
|
|
|
|
table design