Solved

DYNAMIC DATABASE DESIGN

Posted on 2007-03-27
22
2,709 Views
Last Modified: 2013-11-24
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
Comment
Question by:karanba
  • 8
  • 4
  • 3
  • +5
22 Comments
 
LVL 18

Assisted Solution

by:Sham Haque
Sham Haque earned 20 total points
Comment Utility
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
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 60 total points
Comment Utility
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 20 total points
Comment Utility
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 160 total points
Comment Utility
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
 
LVL 4

Assisted Solution

by:opho
opho earned 20 total points
Comment Utility
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
On why you should *not* use that kind of design...

http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 160 total points
Comment Utility
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
<<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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 60 total points
Comment Utility
>>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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 160 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
<<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
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 60 total points
Comment Utility
actually it looks to be in
<Microsoft Zone: dBase>
and not specifically in Access or SQL Server.
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 60 total points
Comment Utility
Racimo ... so how have you handled this in your database designs?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 60 total points
Comment Utility
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 160 total points
Comment Utility
<< 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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 60 total points
Comment Utility
Fair enough.
0
 

Author Comment

by:karanba
Comment Utility
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
<<Racimo ... so how have you handled this in your database designs?>>
Relation subtyping.  
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
<<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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 160 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now