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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 storing product information
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hasanali00
Posting Yak Master

207 Posts

Posted - 09/16/2004 :  09:56:00  Show Profile  Reply with Quote
Hi, I am new to databases.

I want to store product details in a database. The details which i want to store, includes:

(1) product title
(2) image & price
(3) product description:
first description paragraph
second description paragraph
etc...

(4) product features:
colour - red
material - silk
etc...

Now i can easily create table fields to store title, image and price. But how do I store description and product features in a database table. yes, I can create a table field to store description, but when I will access this info, I will not be able to separate the info into paragraphs. Similarly, how do i store product features into a table.

thanks

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 09/16/2004 :  10:58:19  Show Profile  Reply with Quote
I'd suggest a bit of normalisation. I.e.

CREATE TABLE dbo.Products
(
ProductID INT IDENTITY(1, 1) NOT NULL,
ProductDesc NVARCHAR(255) NOT NULL
)
INSERT INTO Products (ProductDesc)
SELECT N'Fancy Cushion'

CREATE TABLE dbo.FeatureCategories
(
FeatureCategoryID SMALLINT IDENTITY(1, 1) NOT NULL,
FeatureCategoryDesc NVARCHAR(255) NOT NULL
)

INSERT INTO dbo.FeatureCategories (FeatureCategoryDesc)
SELECT N'Colour'
UNION SELECT N'Material'

CREATE TABLE dbo.Features
(
FeatureID INT IDENTITY(1, 1) NOT NULL,
FeatureDesc NVARCHAR(255) NOT NULL,
FeatureCategoryID SMALLINT NOT NULL
)

INSERT INTO dbo.Features (FeatureDesc, FeatureCategoryID)
SELECT N'Red', 1
UNION SELECT N'Silk', 2

CREATE TABLE dbo.ProductFeatures
(
ProductID INT NOT NULL,
FeatureID INT NOT NULL
)

INSERT INTO dbo.ProductFeatures (ProductID, FeatureID)
SELECT 1, 1
UNION SELECT 1, 2

CREATE TABLE dbo.Descriptions
(
DescriptionID INT IDENTITY(1, 1) NOT NULL,
DescriptionText NTEXT NOT NULL
)


CREATE TABLE dbo.ProductDescriptions
(
ProductID INT NOT NULL,
DescriptionText NTEXT NOT NULL,
DisplayOrder SMALLINT NOT NULL
)

INSERT INTO dbo.ProductDescriptions (ProductID, DescriptionText, DisplayOrder)
SELECT 1, N'It''s a cushion', 1
UNION ALL SELECT 1, N'It''s red', 2
UNION ALL SELECT 1, N'It''s fancy', 3

Use the order to sort the descriptions. The one-to-many relationship between products and descriptions allows you to associate multiple bits of text (paragraphs if desired) with a product and order them according to the value in the DisplayOrder column.



Mark
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 09/16/2004 :  10:59:28  Show Profile  Reply with Quote
Additionally, as suggested time and again on this forum, it's not a good idea to store images in the DB. You're better off storing them in the file system and storing the location as a string in the DB.

Mark
Go to Top of Page

hasanali00
Posting Yak Master

207 Posts

Posted - 09/16/2004 :  11:21:29  Show Profile  Reply with Quote
This is great. Thanks very much. Things are much clear now.
Go to Top of Page

mwjdavidson
Aged Yak Warrior

United Kingdom
735 Posts

Posted - 09/16/2004 :  11:27:43  Show Profile  Reply with Quote
No problem

Mark
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000