• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2843
  • Last Modified:

DYNAMIC DATABASE DESIGN

Hi,

Hi,

I have a big question about database design. I have some products to keep their properties in database. But there is not a exact template of this products. Some could have 50 fields of property and some have 4 or 5 field of property. And by the time user could add new properties to any product or could delete some of the fields from a product. What could be the best solution.. here I got some but could not think which could be better.

1. Having a template table and store products templates here. By relating them to the real value table got the problem. You can see this will get many inner joins.
2. Hard code to the admin panel and give rights to user to create, alter table.
3. Insert xml as text field in to the database but this time sql still confusing.

Is there an other solution. The solution can be outof the relational database models. But I want to know which will be best.
not: the database will heavily used in the future.  

thanks as now.
0
karanba
Asked:
karanba
  • 8
  • 4
  • 3
  • +5
21 Solutions
 
Sham HaqueSenior SAP CRM ConsultantCommented:
If your properties could be shared across various products, this would be the way to go:

Products            ProdProperties             Properties
PROD_ID            PROPPROD_ID              PROP_ID
PROD_NAM         PROD_ID                      PROP_NAME
                           PROP_ID
                           

select p1.prod_name, p3.prop_name
from products p1
join prodproperties p2 on p2.prod_id = p1.prod_id
join properties p3 on p3.prop_id = p2.prop_id

constantly creating/altering tables does not sound like a good idea....
0
 
stevbeCommented:
tblProductType
ProductTypeID, ProductType
1                       Toaster
2                       Microwave

tblTemplate
ProductTypeID    ProductAttribute
11                         Color
11                         VAC
11                         SliceCount
22                         Color
22                         VAC
22                         CubicInches

tblProduct
tblProductID      ProductName   ProductTypeID
57                     Toasterizer       11
86                     SuperZap          22

and finally you have
tblProductAttribute
ProductID    Attribute    AttributeValue
1                 Color          Red  
1                 VAC            110
1                 SliceCount   2

with this set up you need to include the ProductTypeID in the Prodcut table and when they create a new product they select a product type and you have code that gets the attribute template for the ProductTypeID from tblTemplate and insert the attributes into the tblProductAttribute table.

Yes there are lots of joins but this will give you the best flexibility.

0
 
LowfatspreadCommented:
don't use XML,
don't let the users alter tables on the fly...

are your products really so different?

if you analyses them don't they actually have a core of main attributes?
have you identified them ...

what will happen to the data once  its stored, how do you need to analyse it...

the proposed method of having a vertical definition of values relating to relating to a product can work
well in some circumstances... but it depends on how you need to track changes.. to both the attribute value
and the property model change itself...

e.g. what is a product?  e.g. a car...  does a car running on Petrol differ from a version running on Diesel
what would be the effect of swapping the engine system ?

 
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Here is a design to consider:

tblProductAtributes - One record per attribute type
ProductAttributeID - Autonumber - PK
Description - Text

tblProducts - One record per product
ProductID - PK
Description
...
...
etc

tblProductAttributes - One record per Product/Attribute combination
ProductID - PK A - FK Product table
AttributeID - PK B - FK tblProductAtributes
Value - text

  Now each product can have it's own list of attributes.  Note if you live in the surrogate world, that last table would be:

tblProductAttributes - One record per Product/Attribute combination
ProductAttrbitue ID - Autonumber - PK
ProductID - CK1 A - FK Product table
AttributeID - CK2 B - FK tblProductAtributes

JimD
Value - text
0
 
ophoCommented:
Hey karanba...

The key question, to me, is:  Do you know the full range of product properties?  Or, are the properties expected to be fluid, making it a core requirement of the project that new properties be added easily by users at any time?

If the first is true -- there are a lot of properties, and while many are not applicable to many of the products, the properties are generally stable -- I'd be analyzing them to build tables which fit them.

If the second is true -- adding new properties must be a readily-available function performed by users -- I'd be analyzing, categorizing, grouping the possible types of properties:  Are some of the property values expressed as numbers?  Dates?  Text?  Finite sets of options?  Combinations of all of the above?

Echoing other responses, I'd never allow users to update table structures, I would consider tables of properties/attributes, but I'd also be concerned about the complexity of being dynamic.  Key to the workability of the dynamic approach would be determining whether my client has folks controlling the process who will be able to understand and manage the properties well.  The dynamic properties can still work, but the need for safeguards, edits, help screens, and coaching may increase dramatically.

--Steve
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Building templates of properties for products is known as EAV design.  It just is the worst thing you may do in design.   You will pretty much kill the db only with constraint implementations.   Forget the idea.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
On why you should *not* use that kind of design...

http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
While I would agree with some of those points, I certainly would not agree with all.  And although this is based on an understanding of posts by Joe Cleko, Joe is just one voice among many.  Not everyone agrees with everything he says.

Case in point is Access itself, which does an admirable job of keeping track of different objects with different attributes.

JimD
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<While I would agree with some of those points, I certainly would not agree with all.  And although this is based on an understanding of posts by Joe Cleko, Joe is just one voice among many.  Not everyone agrees with everything he says.>>  I understand your caution for Celko (I have had several exchanges with him which revealed to me his utter ignorance of RM concepts).  Nevertheless, under current SQL dbms EAV are to be avoided because the cost for keeping integrity far outweight any potential benefit.

<<Case in point is Access itself, which does an admirable job of keeping track of different objects with different attributes.>>
So because it works on Access, we should draw conclusion that's the way to go...?;)

0
 
Anthony PerkinsCommented:
>>Case in point is Access itself<<
Since this is the MS SQL Server Zone, I suspect you lost your argument by using MS Access as an example :)

Having said that a better example would probably be LDAP.  This allows you to dynamically add attributes.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Racimo,

<<Nevertheless, under current SQL dbms EAV are to be avoided because the cost for keeping integrity far outweight any potential benefit.>>

 I suppose that depends on what your trying to accomplish and what the benefit might be.

acperkins,

<<Since this is the MS SQL Server Zone, I suspect you lost your argument by using MS Access as an example :)>>
 
   Probably :)  I didn't notice the zone change.  In any case, Access is proof that such a design is possible, can work, and can implement constraints.  Access stores different objects all with different attributes and it does so without a ridged table structure.  Does it work; yes?  Does it work as well as something else could?  I really don't know other then the way it works now does work fairly well.  I could imagine somewhat what a monster it would be if every possible attribute for every possible object was mapped to a specific table field.   Would that be better?  Maybe, and then maybe not.   The point is that such designs are possible and workable.

JimD
 
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Access is proof that such a design is possible, can work, and can implement constraints>>
Possible is not desirable.

<< Does it work; yes?>>Just curious.  What exactly do you consider a *working* or *workable* solution?
0
 
stevbeCommented:
actually it looks to be in
<Microsoft Zone: dBase>
and not specifically in Access or SQL Server.
0
 
stevbeCommented:
Racimo ... so how have you handled this in your database designs?
0
 
Anthony PerkinsCommented:
JDettman,

Ok.  I will put my bias against MS Access on one side, just for a minute though :)

Perhaps you can tell us what you mean by this:
"Access stores different objects all with different attributes and it does so without a ridged table structure. "
For example, would you store the attributes as binary, delimited, how?

Perhaps there is some misunderstanding as to what you mean and what I am understanding you are saying.  If I understand you correctly you can do that in pretty much any database, however the question is it a good idea and how scalable it is.  For example, in MS SQL Server you can store attributes in an xml data type and search those attributes using XPath.  That may be "workable" for up to a few hundred thousand records, but if you plan to go over the million mark, I suspect it should give you some pause for thought.

In this particular case the questioner has stated: "the database will heavily used in the future" and while we have all dreamed up eBay databases in the past, we should take the comment at face value.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< If I understand you correctly you can do that in pretty much any database, however the question is it a good idea and how scalable it is.  >>

  Right.  I'm not saying that it's a good idea to use that type of design for everything and I'm not saying there are not problems with it.  But there are places and times where one might consider using it.  Every approach has pros and cons and we live in the real world, which often restricts us in the approach we might use.  Heck surrogate keys are basically a shortcut for doing a relational database correctly simply because we don't have any real world systems that can do it right (because of performance issues).

  I just think it's wise to consider all approaches before starting down the road.

JimD

 
0
 
Anthony PerkinsCommented:
Fair enough.
0
 
karanbaAuthor Commented:
Thanks for all answers.

This is really a big design problem. But its now on may way. Solution like stevbe's, that use 3 or more tables seems that it is best for flexibility. But It needs some more. Because simple number or text type data will not suitable for all. Some fields will need choice of multiple values, datetime.

And think that this is the database of a products review site. Cameras, watches, tvs, even computer accessories like a mouse, and more could be added as a product. And each group must be search able by giving value on any fields. Like;

Get all the 3 mega pixel and the band of abc firm cameras.

And also editor could select to create a new template or edit an old one.

Now I design and apply this with;

1  products_common_properties_table             : that hold the common properties like cost, release date, label, supplier,.... of a product
2  products_properties_numeric_values_table  : that hold product_id, label_of_properties, value_of_property
3  products_properties_text_values_table        : that hold product_id, label_of_properties, value_of_property
4  products_properties_boolean_values_table  : that hold product_id, label_of_properties, value_of_property

You see the one table missing that hold templates and related properties table. Because of this I need to put "label_of_properties" to each values table. And when editor wants to add a new tv review, he calls last tv entry to  copy and update. By this way he only fills the new values for that table. you see this will kill db, but as a performance solution. Every time a new product is insert or update a related product xml, named  
like products_154623.xml, created or rewrite to disk. And when the related page called by any visitor the pre-compiled xml-xslt output served by site to user. But dynamic search is really a headache. The longest SQL statment that I wrote :)

But Know the system works. This is an approach that use I/O  operations on disk for exporting the whole data as a simple xml file. Maybe that xmls could also hold in an  big  table, I never use but know that sql 2005 could be searched by xpath or xquery.

LDAP may be used but I do a very little about it. I only here that its it fast on reading but slow on writing. At least it could be get more complexity to code. And last of all &#305; will check Racimo URLS to see if there is a new approach.

Thanks again and please write here for suggestions.

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Racimo ... so how have you handled this in your database designs?>>
Relation subtyping.  
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Hi Anthony,

<<Ok.  I will put my bias against MS Access on one side, just for a minute though >>
The solution of EAV is as wrong on Access as it is on SQL Server.   Most economic solution on that kind of problem is relation subtyping (on any platform).
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Right.  I'm not saying that it's a good idea to use that type of design for everything and I'm not saying there are not problems with it.>>
I understand your perspective pro/cons.   But what good is a solution if it only works on a small db?  Especially when the user specifies that the db is meant to grow?

<<I just think it's wise to consider all approaches before starting down the road.>>
Fair enough.

Regards...;)
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
karanba,

Run away from XML!  XML is just the most unefficient treatment of data you can imagine on a database.  

Hope this helps...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 4
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now