Database design question

  • 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

  • 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

  • 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.
  • 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. Selburg
  • 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.

  • 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. 🙂

  • 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. Selburg
  • 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

  • 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!

  • 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. Selburg
  • 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.

  • 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

  • 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

  • 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

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • and SQL does kick butt

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply