Products with different options

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

General SQL Server Forums

Database Design and Application Architecture

Products with different options

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2012-01-09 : 04:53:31

Hello,

I have table:

tbl_Products:
Id, Product
1, LCD
2, Computer
3, Telephone

and

tbl_Attributes:
Id, ProductsId, Attributes
1, 1, color:black, resolution:1024,etc...

The second table I have because imagine that I have a lot of different information. In this case, could create a lot of columns that contain NULL values??.

In the table tbl_Attributes is best to save as XML format?
Do you have any example how to do this?

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 06:17:20

have you read about normalisation? why store multiple attribute values inside same column . ideally design should be like


tbl_Products  Id, Product

tbl_Attribute_Type  Id_Attribute_Type, Type_Name

tbl_Attributes Id,ProductsID,AttributeTypeId,Value


and the values will be like

tbl_Products:
Id, Product
1, LCD
2, Computer
3, Telephone

tbl_Attribute_Type  
Id_Attribute_Type, Type_Name
1,color
2,resolution ...


tbl_Attributes:
Id, ProductsId, AttributeTypeId,Value
1, 1, 1, black, 
2, 1, 2, 1024

this will make sure DML operations are optimised and DML anamolies dont occur by avoiding redundancy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2012-01-09 : 06:48:49

quote:
Originally posted by visakh16

have you read about normalisation? why store multiple attribute values inside same column . ideally design should be like

tbl_Products Id, Product

tbl_Attribute_Type Id_Attribute_Type, Type_Name

tbl_Attributes Id,ProductsID,AttributeTypeId,Value

and the values will be like

tbl_Products:
Id, Product
1, LCD
2, Computer
3, Telephone

tbl_Attribute_Type
Id_Attribute_Type, Type_Name
1,color
2,resolution ...

tbl_Attributes:
Id, ProductsId, AttributeTypeId,Value
1, 1, 1, black,
2, 1, 2, 1024

this will make sure DML operations are optimised and DML anamolies dont occur by avoiding redundancy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks.

And if you want to store more data in the table of at the same time with one click?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 07:34:08

you can always pass data in recordset or in array format like XML to db and parse individual values from XML to store onto table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2012-01-09 : 08:14:54

quote:
Originally posted by visakh16

you can always pass data in recordset or in array format like XML to db and parse individual values from XML to store onto table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Why not insert at same time
value1, value2, value3

Check code:

ALTER procedure sp_save

@Value1 varchar(50),
@Value2 varchar(50),
@Value3 varchar(50)
/*@Value2 varchar(50),
@Value3 varchar(50)*/

AS

BEGIN

Insert into table1

(
Value
)
Values
(
@Value1+','+@Value2+','+@Value3
)

END

Inserted value1, value2, Value3 - this is wrong.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 08:36:04

hmm...didnt understand why you're again going for csv approach
did you understand what i explained earlier?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2012-01-09 : 08:50:37

quote:
Originally posted by visakh16

you can always pass data in recordset or in array format like XML to db and parse individual values from XML to store onto table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What exactly do you mean?
Do you have a case?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 08:57:47

quote:
Originally posted by programer

quote:
Originally posted by visakh16

you can always pass data in recordset or in array format like XML to db and parse individual values from XML to store onto table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What exactly do you mean?
Do you have a case?

see

http://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

programer
Posting Yak Master

221 Posts

Posted - 2012-01-09 : 09:18:13

quote:
Originally posted by visakh16

quote:
Originally posted by programer

quote:
Originally posted by visakh16

you can always pass data in recordset or in array format like XML to db and parse individual values from XML to store onto table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What exactly do you mean?
Do you have a case?

see

http://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

We can explain how to searched at different criteria?
If I have: IBAN: 4237492879842, SWIFT: DHAJK.

If I want to get various data how do I get them?

I think so this is no the best solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 09:47:04

quote:
Originally posted by programer

quote:
Originally posted by visakh16

quote:
Originally posted by programer

quote:
Originally posted by visakh16

you can always pass data in recordset or in array format like XML to db and parse individual values from XML to store onto table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

What exactly do you mean?
Do you have a case?

see

http://visakhm.blogspot.com/2010/04/using-xml-to-batch-load-master-child.html

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

We can explain how to searched at different criteria?
If I have: IBAN: 4237492879842, SWIFT: DHAJK.

If I want to get various data how do I get them?

I think so this is no the best solution.

i dont understand what you're telling
post some sample data and then explain what you want
without it nobody can understand what you really want as we cant see your system nor read your mind!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources