Database Experts Opinions please.

[ketchupaholic]

Okay, at work I'm having a big disagreement with the project management team who has no database experience at all. Here is the issue.

We have 10 product lines, in each product line there are certain number of attribute names, such as height, weight, color, price and so on. We have a total of 670 attribute names for all 10 product lines. The project management team is wanting to create a master file in excel that contains all 670 attribute names, regardless if the attribute name pertains to the product line or not. So let's say 'Widget A' has only 70 attribute names, they want to still add the other 600 attribute names and enter a dash for the value (You can only import if the value has a character in the field). We have to import each product line one-by-one since that is what the software requires.

To me, this is not the proper way to build a database since we are creating a database that will have hundreds of thousands of null characters. They say this is normalizing the databse, and I told them it is he complete opposite of normalizing a database.

I look forward to your opinions as well.

[sknake]

What database are you using?

I have a similar application and what we did is something like the following (assuming you can run this MSSQL query):

IF OBJECT_ID('ItemMaster', 'U') IS NOT NULL DROP TABLE ItemMaster
IF OBJECT_ID('ItemAttributes', 'U') IS NOT NULL DROP TABLE ItemAttributes
GO
Create Table ItemMaster
(
  ItemNumber varchar(30) PRIMARY KEY,
  Description varchar(30),
  SalePrice money --This is an attribute they _all_ have in common
)

Create Table ItemAttributes
(
  AttributeId int identity(1000, 1) PRIMARY KEY,
  ItemNumber varchar(30),
  AttributeName varchar(30),
  AttributeValue varchar(30)
)
GO
Insert Into ItemMaster (ItemNumber, Description, SalePrice) Values ('ABC123', 'Sofa', 50.25)
Insert Into ItemAttributes (ItemNumber, AttributeName, AttributeValue) Values ('ABC123', 'Color', 'Red')
Insert Into ItemAttributes (ItemNumber, AttributeName, AttributeValue) Values ('ABC123', 'Color', 'Blue')
Insert Into ItemAttributes (ItemNumber, AttributeName, AttributeValue) Values ('ABC123', 'Weight', '100')
Insert Into ItemAttributes (ItemNumber, AttributeName, AttributeValue) Values ('ABC123', 'Height', '6.2"')
GO

Print 'View All Items'
Select *
From ItemMaster
Where ItemNumber = 'ABC123'

Print 'View all attributes for a piece'
Select *
From ItemAttributes
Where ItemNumber = 'ABC123'

Print 'View all colors for a piece'
Select *
From ItemAttributes
Where ItemNumber = 'ABC123' and AttributeName = 'Color'

Resulting in:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
View All Items
ItemNumber                     Description                    SalePrice
------------------------------ ------------------------------ ---------------------
ABC123                         Sofa                           50.25

(1 row(s) affected)

View all attributes for a piece
AttributeId ItemNumber                     AttributeName                  AttributeValue
----------- ------------------------------ ------------------------------ ------------------------------
1000        ABC123                         Color                          Red
1001        ABC123                         Color                          Blue
1002        ABC123                         Weight                         100
1003        ABC123                         Height                         6.2"

(4 row(s) affected)

View all colors for a piece
AttributeId ItemNumber                     AttributeName                  AttributeValue
----------- ------------------------------ ------------------------------ ------------------------------
1000        ABC123                         Color                          Red
1001        ABC123                         Color                          Blue

(2 row(s) affected)

[ithelp]

Ask them what to do with record having NULL character ?