storing product information - SQL Server Forums

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)

storing product information

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2004-09-16 : 09:56:00

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

735 Posts

Posted - 2004-09-16 : 10:58:19

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

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-16 : 10:59:28

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

hasanali00
Posting Yak Master

207 Posts

Posted - 2004-09-16 : 11:21:29

This is great. Thanks very much. Things are much clear now.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-16 : 11:27:43

No problem

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources