January 23, 2012 at 11:47 am
I have a relational question about a database I am going to be setting up. It is a database of home appliances.
We have a current database with this database but we are looking to redesign some of the relationships.
Currently for every home appliance type in the database there is a table. For example for Refrigerators there is a table and for clothes washer there is another table. So you can imagine anytime we need to create a new type of appliance it is a maintenance nightmare because a new table has to be created. In each table there are key fields that are the same in every appliance table (Manufacturer, Model, Brand, and Status).
All of the other columns can be unique to each appliance such as MaxWatts, RegulatoryStatus, Fan Motor Type, Fan Motor Horsepower, etc. I would call those other fields properties of the appliance. Some of those fields may or may not be common to some other appliances but a majority of them are unique to each appliance.
So my idea to set it up relationally a bit better is to create two tables. One table would have the key columns ((Manufacturer, Model, Brand, and Status) and a primary key ApplianceID that would be set as identity column. I would call the first table [Appliance]. The 2nd table would contain all the appliance property information. I would call the 2nd table [ApplianceProperties]. The 2nd table would have a primary key of ApplianceID and be a foreign key into the Appliance table. Additionally to avoid having a different table for each appliance's unique properties I would create about 40 (I dont think there are more unique fields) generic columns. One column would hold the name of the Property (i.e. Fan Motor Type), the 2nd column would hold the actual value associated to the property, and a 3rd column would hold the datatype (i.e. int, decimal, varchar). So I would have Property1Name, Property1Value, Property1Type, Property2Name, Property2Value, Property2Type, etc.
The type of the value field would probably need to be varchar(max) to hold whatever value will be in this column. Then when I need to retrieve the data do a conversion to the Type specified in the PropertyType column.
Does this sound like a decent design for this situation or can anyone thing of a better way to do this that will be low maintenance when I need to add a new appliance? Creative ideas are welcome. If you think my idea is completely idiotic please let me know but in a nice way.
Thanks
January 23, 2012 at 11:55 am
I think you have a good start, but I would not have 1 table for the properties. This can cause you big problems later on. I would set up a separate table for each of the items for their properties. Although, I am not nearly as experienced as others on here, so let's see what everyone else has to say 🙂
Jared
CE - Microsoft
January 23, 2012 at 11:57 am
If you want to go this path you may want to consider to move out of AppliancesProperties the "catalog" columns meaning, create a separate table called ApplicancesPropertiesCatalog where you describe each general-purpose column based on applicance type.
This way you will have the catalog function centralized and less overhead on AppliancesProperties table.
Be sure you document it really well 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 23, 2012 at 11:57 am
Break down the properties into their own set.
Something similar to ...
Applicance
ApplianceId INT
Model VARCHAR(50)
Manufacturer VARCHAR(50)
etc...
Properties
propertyID INT
propertyName VARCHAR(256)
ApplianceProperty
applianceID INT
propertyID INT
value SQL_VARIANT
Although, you also may want to have Manufacturers in another table as well.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 23, 2012 at 12:00 pm
The first idea that comes to mind is that in your second table you are going to limit yourself to the number of properties that can be added. In addition I would have some questions to if all your items need 40 properties. Have you considered having a one to many relationship there? So your second table would have PropertyName, PropertyVaule. Then you could add key over to the first table and then a Property Type table.
January 23, 2012 at 12:07 pm
Jason, that is an outstanding design you have suggested. I think that will work very well. I think it also handles Chris' concern over limiting to 40 properties. Each appliance in this design could have either one property or 100 if need be. This is great. Documenting well of course would be prudent.Thanks for the help. I knew I could find someone on here with great ideas and more experience than myself. 🙂
January 23, 2012 at 12:15 pm
SQLKicksButt (1/23/2012)
Jason, that is an outstanding design you have suggested. I think that will work very well. I think it also handles Chris' concern over limiting to 40 properties. Each appliance in this design could have either one property or 100 if need be. This is great. Documenting well of course would be be prudent.Thanks for the help. I knew I could find someone on here with great ideas and more experience than myself. 🙂
Be aware though, that if you plan on joining the table using the [value] field it can get a little tricky.
See http://msdn.microsoft.com/en-us/library/ms173829.aspx for more info.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 23, 2012 at 12:21 pm
My thoughts on this approach are mixed. Not being as experienced as Jason, I wonder what the implications are of updating an appliance or entering a new one. I think you have to think about these things. For example, if you have a new appliance you may not only have to insert 40 rows for 40 properties of this, but you may also have to insert new rows into your catalog. I would ask how often do you add a new appliance? How often do you add new properties? Also, how often do you add a new class of appliances (refrigerators, dishwashers,etc.). How are you using this data (entered though web application or some other form or is this a reference database where all data is manually entered and relatively fixed)?
Jason et al, I would assume that depending on the answers to these questions you may suggest something different? Maybe not... Thoughts?
Jared
CE - Microsoft
January 23, 2012 at 12:26 pm
I would say 99% of the time there will be no joining on any of the property value columns but there will be comparisons made to them (i.e. [value] > 23.94, round( [value], 2) > 4.35, [value] = 'A' ). I have never used the SQL_Variant type but it looks like it will do what I need it to do. Thanks again!
January 23, 2012 at 12:28 pm
As with anything in SQL, it depends. This is more than one way to get the desired results and my suggestion is in no way intended to be taken as the "best".
You could just as easily have a column for each datatype
ApplianceProperty
applianceID INT
propertyID INT
IntValue INT NULL
CharValue VARCHAR(256) NULL
DecimalValue NUMERIC(18,2) NULL
... but I'm sure there are cons to this approach as well.
All in all, you'll need to think through each possible design, ask for suggestions (like you did here) and test test test.
The design portion of database development is arguably the most important phase. The best code can't make up for a poorly designed model.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 23, 2012 at 12:35 pm
Jared, you are correct that there could be different implications for adding a new appliance versus just updating. I would say adding a new appliance is a rare thing for us (maybe once a year) but when it does happen its a mini-project in its impact (at least two weeks of work). Adding appliance records to a particular category happens daily. But adding or changing existing properties for an appliance is pretty much monthly. This system is web based using ASP.NET. I would have to create a new interface to handle the catalog of the properties table that is used by all appliances. I would also have to create a new interface to add, update, delete the appliance and that interface would also handle the property associations to the appliance.
Thanks for everyone's help and I surely will keep my mind open to other alternatives to this design but I think I have a good start and I will continue to think of the possible cons and perfect the design for our situation.
January 23, 2012 at 12:43 pm
SQLKicksButt (1/23/2012)
Jared, you are correct that there could be different implications for adding a new appliance versus just updating. I would say adding a new appliance is a rare thing for us (maybe once a year) but when it does happen its a mini-project in its impact (at least two weeks of work). But adding or changing existing properties for an appliance is pretty much monthly. This system is web based using ASP.NET. I would have to create a new interface to handle the catalog of the properties table that is used by all appliances. I would also have to create a new interface to add, update, delete the appliance and that interface would also handle the property associations to the appliance.
So that's where I have some problems with this approach. When you submit a form via asp.net to SQL Server for an update, I assume you are updating several properties at once.
So instead of:
UPDATE refrigeratorProperties
SET columnA = @columnA, columnb = @columnb, ...
WHERE applianceID = 1234
You will now have:
UPDATE properties
SET value = @valuea
WHERE propertyid = 1 AND applianceID = 1234;
UPDATE ...;
UPDATE ...;
To me the several updates seems to be a lot more work and will probably be more resource intensive. Not to mention joins to the catalog and such. I don't know, to me it would be less maintenance and better performance of the application to separate the properties of the different classes where they are not uniform across all classes. Just my opinion, but I wouldn't want to have to code all of those update statements separately.
Jared
CE - Microsoft
January 23, 2012 at 1:22 pm
Jared,
My idea was that the ASP.NET form would have a grid that would have the property name and the propertyID and ApplianceID in hidden fields.
When the user adds a property they would pull up a selection window that would have all the items in the properties table and add to the Appliance form. When the user would hit save on the Appliance form it would loop through the items in the grid and call a stored procedure that has one insert or update depending on whether it already exists. So one call for item in the grid. I definitely can see where I might need to tweak the design a bit but it has given me a start. I appreciate all of your input as well as the others.
David
January 23, 2012 at 4:11 pm
As everyone else alluded, whenever you have potential for many attributes for a single instance, it is generally better to build your tables vertically (rows instead of columns). It is a little more work getting the data in and out, but far more manageable and extensible.
That being said, I'd normalize it more. For instance, take the Manufacturer text out of the product table, use an ID instead. You likely have many appliances made by the same manufacturer.
Adding types and groups will allow you to retrieve data more specifically, for example use the ApplianceGroupType to find all Kitchen appliances, as in the final statement below
So, if you want to get reallllly normal (I created all the tables as temp tables, so the references are ignored) (This isn't even realllly normal. just sort of normal. if you try to insert values, you will see where you probably will need even more granularity. or maybe not.) :
And yeah, the column names are really long and descriptive. Personal preference.
create table #Manufacturer (
ManufacturerID INT NOT NULL IDENTITY(1,1) ,
ManufacturerName VARCHAR(50) NOT NULL,
ManufacturerOrderBy SMALLINT NULL,
ManufacturerAdded DATETIME NOT NULL DEFAULT getDate(),
ManufacturerDeleted DATETIME NULL,
--whatever info you need specific to manufacturer, capture who added or deleted, etc.
CONSTRAINT pk_ManufacturerID primary key (ManufacturerID)
)
go
--drop table #Manufacturer
--add some fake data
insert into #Manufacturer (ManufacturerName, ManufacturerOrderBy)
select 'General Electric',5 union all
select 'LP',10 union all
select 'Amana',15 union all
select 'Your Mom',20 union all
select 'Frigidaire',25 union all
select 'Acme',30 union all
select 'The Borg',35 union all
select 'Halliburton',40
;
----select * from #Manufacturer
create table #ApplianceGroupType (
ApplianceGroupTypeID INT NOT NULL IDENTITY(1,1),
ApplianceGroupType VARCHAR(50) NOT NULL,
ApplianceGroupTypeDesc VARCHAR(255) NULL,
ApplianceGroupTypeOrderBy SMALLINT NULL,
ApplianceGroupTypeAdded DATETIME NOT NULL DEFAULT getDate(),
--ApplianceGroupTypeAddedBy INT NOT NULL, --include if you want to capture user, and you have user table
ApplianceGroupTypeDeleted DATETIME NULL,
--ApplianceGroupTypeDeletedBy INT NOT NULL, --include if you want to capture user
CONSTRAINT pk_ApplianceGroupTypeID PRIMARY KEY (ApplianceGroupTypeID)
)
GO
--add some fake data
insert into #ApplianceGroupType (ApplianceGroupType, ApplianceGroupTypeDesc, ApplianceGroupTypeOrderBy)
select 'Residential Kitchen','Appliances that are typically used in a residential kitchen',5 union all
select 'Commercial Kitchen','Appliances that are typically used in a commercial kitchen',10 union all
select 'Laundry','Appliances that are typically used in a laundry room',15 union all
select 'HVAC','Equipment used in heating, ventilations and air conditioning',20 union all
select 'Home Entertainment','Appliances used for entertainment including TV and Home Audio',25
;
----select * from #ApplianceGroupType
create table #ApplianceType (
ApplianceTypeID INT NOT NULL IDENTITY(1,1),
ApplianceGroupTypeID INT NOT NULL REFERENCES #ApplianceGroupType(ApplianceGroupTypeID),
ApplianceType VARCHAR(50) NOT NULL,
ApplianceTypeDesc VARCHAR(255) NULL,
ApplianceTypeOrderBy SMALLINT NULL,
ApplianceTypeAdded DATETIME NOT NULL DEFAULT getDate(),
ApplianceTypeDeleted DATETIME NULL,
CONSTRAINT pk_ApplianceTypeID PRIMARY KEY (ApplianceTypeID)
)
GO
--add some fake data
insert into #ApplianceType(ApplianceGroupTypeID, ApplianceType, ApplianceTypeDesc, ApplianceTypeOrderBy )
select 1, 'Refrigerator' , 'Big box that keeps food cold', 5 union all
select 1, 'Microwave Oven' , 'Smaller box that makes food hot', 10 union all
select 2, 'Super Chiller' , 'Instant freezer for restaurant', 15 union all
select 4, 'Window Air Conditioner' , 'Portable AC unit that mounts in window', 20 union all
select 5, 'Karaoke Player' , 'Annoys friends and neighbors', 25 union all
select 3, 'Gas Dryer' , 'Magically dries clothes', 30
;
--select * from #ApplianceType
create table #Appliance (
ApplianceID INT NOT NULL IDENTITY(1,1),
ApplianceTypeID INT NOT NULL REFERENCES #ApplianceType(ApplianceTypeID),
ManufacturerID INT NOT NULL REFERENCES #Manufacturer(ManufacturerID),
ApplianceName VARCHAR(255) NOT NULL,
ApplianceDescription VARCHAR(MAX) NULL,
Model VARCHAR(255) NULL,
ApplianceOrderBy SMALLINT NULL,
ApplianceAdded DATETIME NOT NULL DEFAULT getDate(),
ApplianceDeleted DATETIME NULL,
CONSTRAINT pk_ApplianceID PRIMARY KEY (ApplianceID)
)
GO
--add some fake data
insert into #Appliance(ApplianceTypeID, ManufacturerID,ApplianceName,ApplianceDescription, Model, ApplianceOrderBy )
select 1, 1, 'Side Out','Upright fridge with side by side doors','EEI89872930847', 5 union all
select 1, 3, 'Serial Killer Special','Locker style freezer with 10 cubic feet of storage for those hard to dispose of items','IJS_3j3j3j', 10 union all
select 3, 5, 'The Dryinator','','oiweurpoewiru132456', 15
--select * from #Appliance
create table #ApplianceAttributeGroupType (
ApplianceAttributeGroupTypeID INT NOT NULL IDENTITY(1,1),
ApplianceAttributeGroupType VARCHAR(50) NOT NULL,
ApplianceAttributeGroupTypeDesc VARCHAR(255) NULL,
ApplianceAttributeGroupTypeOrderBy SMALLINT NULL,
ApplianceAttributeGroupTypeAdded DATETIME NOT NULL DEFAULT getDate(),
CONSTRAINT pk_ApplianceAttributeGroupTypeID PRIMARY KEY (ApplianceAttributeGroupTypeID)
)
--add some fake data
insert into #ApplianceAttributeGroupType(ApplianceAttributeGroupType, ApplianceAttributeGroupTypeDesc, ApplianceAttributeGroupTypeOrderBy )
select 'Regulatory','Attributes that relate to regulatory standards',5 union all
select 'Specifications','Stuff about the parts and whatnot',10 union all
select 'Energy','Energy star ratings and electrical',15 union all
select 'Dimensions','Describes the appliance in terms of size and shape',20
--select * from #ApplianceAttributeGroupType
create table #ApplianceAttributeType (
ApplianceAttributeTypeID INT NOT NULL IDENTITY(1,1),
ApplianceAttributeGroupTypeID INT NOT NULL REFERENCES #ApplianceAttributeGroupType(ApplianceAttributeGroupTypeID),
ApplianceAttributeType VARCHAR(50) NOT NULL,
ApplianceAttributeTypeDesc VARCHAR(255) NULL,
ApplianceAttributeValueDataType VARCHAR(25) NULL,
ApplianceAttributeTypeOrderBy SMALLINT NULL,
ApplianceAttributeTypeAdded DATETIME NOT NULL DEFAULT getDate(),
CONSTRAINT pk_ApplianceAttributeTypeID PRIMARY KEY(ApplianceAttributeTypeID)
)
--add some fake data
insert into #ApplianceAttributeType(ApplianceAttributeType, ApplianceAttributeGroupTypeID, ApplianceAttributeTypeDesc, ApplianceAttributeValueDataType,ApplianceAttributeTypeOrderBy )
select 'Max Watts',3,'Describes the maximum allowable wattage for the thing','Integer',10 union all
select 'Motor Type',2,'What type of motor does the appliance have','Varchar(max)',15 union all
select 'Status Review Date',1,'When does the product review expire','Datetime',20 union all
select 'Height',4,'Product height','Varchar(max)',25
--and finally - what you were after all along
create table #ApplianceAttributes (
ApplianceID INT NOT NULL REFERENCES #Appliance(ApplianceID)
,ApplianceAttributeTypeID INT NOT NULL REFERENCES #ApplianceAttributeType(ApplianceAttributeTypeID)
,ApplianceAttributeValue VARCHAR(MAX) NOT NULL,
constraint pk_ApplianceAttribute primary key (ApplianceID, ApplianceAttributeTypeID)
--use a composite key to keep the values unique
)
--and add some data
insert into #ApplianceAttributes(ApplianceID, ApplianceAttributeTypeID,ApplianceAttributeValue)
select 1, 3, '2012-11-29' union all
select 1, 4, '6 feet 6 inches' union all
select 2, 3, '2015-01-01' union all
select 2, 1, '60' union all
select 3, 2, 'Magic Motor' union all
select 3, 4, '1 meter'
--see what you added
select * from #Appliance
select * from #ApplianceAttributeGroupType
select * from #ApplianceAttributeType
select * from #ApplianceAttributes
select * from #ApplianceGroupType
select * from #ApplianceType
select * from #Manufacturer
--now get some
Select
A.ApplianceName
,M.ManufacturerName
,AAGT.ApplianceGroupType
from
#Appliance a
inner join #Manufacturer m on m.ManufacturerID = a.ManufacturerID
inner join #ApplianceAttributes aa on aa.ApplianceID = a.ApplianceID
inner join #ApplianceAttributeType aat on aat.ApplianceAttributeTypeID = aa.ApplianceAttributeTypeID
inner join #ApplianceGroupType aagt on aagt.ApplianceGroupTypeID = aat.ApplianceAttributeGroupTypeID
and aagt.ApplianceGroupType = 'Residential Kitchen'
--clean up
drop table #Appliance
drop table #ApplianceAttributeGroupType
drop table #ApplianceAttributeType
drop table #ApplianceAttributes
drop table #ApplianceGroupType
drop table #ApplianceType
drop table #Manufacturer
January 23, 2012 at 6:16 pm
and SQL does kick butt
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply