Solved

Need advise on database design

Posted on 2012-04-01
28
297 Views
Last Modified: 2012-04-06
hello,

I'm dealing with some products which are presented base on production line, series, sub-series and few other parameters (leveling).

let's say we have:

two production lines:
1) a
2) b

each production line has its series and sub-series and etc.

1) a
  1-1) 1
      1-1-1) 11
       1-1-2) 12

2) b
  2-1) 1
     2-1-1) 11
     2-1-2) 12

While end user goes through the tree a part number will be assigned to each product e.g.
a111  or a112 or from second production line we can consider b112 another part number.

Now, questions:

1) I'm considering  (let's say for only 3 levels e.g. production line, series and sub-series) 3 tables ... production-line tables where it has:  
PK  prod_id
      desc

series table:  (composite Primary key using prod_id and series_id)
PK  fk  prod_id
PK       series_id
           desc


sub-series table:  (composite Primary key  using prod_id, series_id, and subseries_id)
PK  fk  prod_id
PK  fk  series_id
PK       subseries_id
           desc

Question:

1) Is this a right approach?
2) is there any better or optimized way to do this?
3) last question, as you see information are stored in 3 tables and each product part number is generated base on the selected path by end-user. Now, is it also OK to have a different table with the full information of part number table only for search and locating a specific product?

Any help is appreciated great.

Regards,
akohan
0
Comment
Question by:akohan
  • 13
  • 10
  • 5
28 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 500 total points
ID: 37793719
0
 
LVL 57
ID: 37793724
<<1) Is this a right approach?>>

   No.  What you want is a single table table that describes the structure and a table to describe the parts.  See:


http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_19-BOM-parts-list-expansion.html

<<2) is there any better or optimized way to do this?>>

 See #1.

<<3) last question, as you see information are stored in 3 tables and each product part number is generated base on the selected path by end-user. Now, is it also OK to have a different table with the full information of part number table only for search and locating a specific product?>>

  See #1.

Jim.

>>
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37793740
Using the nested set method, the structure would look as follows for N levels deep series...You could even attach specific sub series to a product_line.

product_line: id, descr
series: name, product_line(FK-->product_line(id)), start, end
0
 
LVL 57
ID: 37793826
Oops, strayed into the wrong topic area, so what I posted is not quite on.  

  I do however have a SQL only solutions to doing BOM's, which I've attached.  One's by Joe Cleko and the other is from nico here on EE.

Jim.
NestedBOM.zip
NestedBOM-nico5038.zip
0
 

Author Comment

by:akohan
ID: 37793956
Jim,
Are you saying your first solution was not related?

Thanks.
0
 

Author Comment

by:akohan
ID: 37793959
Hi Racimo,

I'm not sure if I follow you comment. Are you saying that two tables can cover for production-lines, series, sub-series and styles?

Thanks.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37793967
<<I'm not sure if I follow you comment. Are you saying that two tables can cover for production-lines, series, sub-series and styles?>>
In your case, a nested set approach would allow 2 tables to represent production_lines, series and sub_series on N-levels for series and sub series.  Dunno about styles(u just brought it up:)).  Please read and understand how to use Nested model to represent hierarchies and you will be able to respond to this question on your own.

Hope this helps...
0
 

Author Comment

by:akohan
ID: 37794061
oh from style I mean sub-subSeries it is the 4th level.
level 1  production
level 2 series
level 3 subseries
level 4  style


I am looking at the concept you have brought up and thanks for sharing it. Also, I see managing data in a hierarchical way as discussed here http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Any idea if what you are pointing to is the same as this concept?

Thanks.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37794701
Nested set is one way to represent hierarchies in relational db
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37794709
In short...yes. The link talks also about nested set
0
 

Author Comment

by:akohan
ID: 37794712
Great! thank you Racimo
0
 
LVL 57
ID: 37795711
<<Jim,
Are you saying your first solution was not related?
>>

  In terms of table design, yes it was related, but in terms of the article I pointed you to, the example was in Access with a VBA procedure to do the interation, not SQL.  So it was off in that regard.

<<I'm not sure if I follow you comment. Are you saying that two tables can cover for production-lines, series, sub-series and styles?>>

  You asked this of Racimo, but it's the same thing I was pointing you to and the answer is yes.  You can represent multiple nested level with two tables and actually, you only really need one.

Jim.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37795744
<<You can represent multiple nested level with two tables and actually, you only really need one.>>
You pointed out to the Adjacency method which is one of the three methods to correctly represent hierarchies in a relational system.

One should also keep in mind that representing a hierarchies in a relational database using only a single table is known flawed design for the following reasons:

> Top parent does not have the same cardinality to itself that to its siblings.  A logical flaw.
> Representing a hierarchy in a single physical table is a recipe for self table locking disaster. Performance is really bad when number of concurrent transaction increases on the table.

Only Adjacency List, Nested Set Model and Nested Intervall model are known to be correct relational representations of hierarchies, both logically and physically.  Nested Set and Nested Interval are the most performant on N level.
0
 
LVL 57
ID: 37795931
<<> Top parent does not have the same cardinality to itself that to its siblings.  A logical flaw>>

  Not sure what you mean here.  Cardinality refers to the amount of uniqueness.   If your saying that some parents may represent something that cannot be used as a child while another can, then that is correct.  That's generally why a two table approach is used.   But it really doesn't stop you from representing a hierarchy with a single table.

<<> Representing a hierarchy in a single physical table is a recipe for self table locking disaster. Performance is really bad when number of concurrent transaction increases on the table.>>

   Not when you maintain assemblies on a level by level basis (single parent / children level at a time).

   I have designed and worked with mfg systems since the early 80's and I've never had locking issues.

<<Only Adjacency List, Nested Set Model and Nested Intervall model are known to be correct relational representations of hierarchies, both logically and physically.  Nested Set and Nested Interval are the most performant on N level.>>

  Don't mix apples and oranges there; the relational model is only concerned with the logical representation of data.  It has nothing to do with the physical implmentation of a model in a given system.

Jim.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:akohan
ID: 37802228
Hello again,

I'm looking at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

and there is a query which I believe will not work properly if two parents (in 3rd level - where their parents in level 2 are different). Just to make it clear, let's say instead of
MP3 Player (parent in 3rd level) we have LCD and this specific LCD has FLASH as child (of course I'm trying to make a point). Now, if you run following query it will not return a single path. Any comment is appreciated.


SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY node.lft;

Open in new window

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37802382
<<Cardinality refers to the amount of uniqueness.   >>
Cardinality has specific meaning in relational model.  It  simply refers to the number of tuples in a  specific relation. Cardinality is a set theory term.

<< If your saying that some parents may represent something that cannot be used as a child while another can, then that is correct.  That's generally why a two table approach is used.   >>
As far as the  relational model is concerned  all tuples in a relation referencing another relation should have the same cardinaliy. A self referencing relation is a flawed hierarchical representation in relational since the top parent can not have a 1:M to itself.  Appart from locking which is a physical problem, the self referencing table/relation is logically wrong.    Adjacency list and Nested Set are correct because they have implement consistent cardinalities.

<<But it really doesn't stop you from representing a hierarchy with a single table.>>
Why not doing right for a change from the beginning.

<<Not when you maintain assemblies on a level by level basis (single parent / children level at a time).>>
How about correctly representing hierarchies (in relational model) and not doing any maintenance at all?

<< I have designed and worked with mfg systems since the early 80's and I've never had locking issues.>>
Guess I had a difference experience and different workload.  

On above 100 transactions/sec and specific table size, I would not thrust anybody who tells he never faced locking and correctness issues due to self referencing issues.

<<Don't mix apples and oranges there; the relational model is only concerned with the logical representation of data. >>
If relational model is only concerned with logical representation how do you explain that JOIN is a relational operator.
Seriously,structure is only one aspect of RM but integrity manipulation, correctness are some of the many others. For some reason, you seem to underestimate RM scope.  Example

<<It has nothing to do with the physical implmentation of a model in a given system.>>
Even though one of the primary purpose of relational model is to create independence between the logical model and the physical implementation, A flawed relational logical model necessarily produces a flawed/underoptimized physical implementation.  

Hope this helps.
0
 
LVL 57
ID: 37802714
<<It has nothing to do with the physical implmentation of a model in a given system.>>
Even though one of the primary purpose of relational model is to create independence between the logical model and the physical implementation, A flawed relational logical model necessarily produces a flawed/underoptimized physical implementation.  >>

  Sorry, but it's the only purpose and that's not what you said.

Jim.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37802749
Let people make their mind about that...
0
 

Author Comment

by:akohan
ID: 37802752
Base on the article at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Will the locking part (to update left and right info) cause any trouble in future?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37802933
<<Will the locking part (to update left and right info) cause any trouble in future?>>
Assuming the right indexes in place (make sure the left right are both physically sorted on disk...On SQL Server we make sure the two columns are included in a clustered index to achieve that), Nested set representation locks very little when navigating the hierarchy (much much less than self referencing tables..)

hop this helps...
0
 

Author Comment

by:akohan
ID: 37803085
Thanks Racimo,

So help me to understand please! when you say right indexes, do you mean right and left columns should be indexed?

For time being I have only a primary key (incremental) for category_id. Please advise.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37803167
<<So help me to understand please! when you say right indexes, do you mean right and left columns should be indexed? >>
Yes.  They should be uniquely indexed and sorted sequentially on disk for maximum performance.  That is pretty much your real logical primary.

<<For time being I have only a primary key (incremental) for category_id. Please advise.>>
A surrogate key is useless if no logical primary key is defined.  You can either:
> Keep the surrogate key but you will have to drop it, declare left/right as a clustered primary key (or clustered unique index)
> Not use a surrogate key and simply drop the category

No matter what you must make sure you have a clustered unique index or clustered primary key on the left/right columns.
0
 

Author Comment

by:akohan
ID: 37803626
1) I'm assuming you mean left and right must be indexed individually, not together. Right?
2) it seems having a primary key as category_id is not a big deal here and only indexing left and right are the vital part of it. am I getting it?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37804752
Together is best.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37804753
Yes
0
 

Author Comment

by:akohan
ID: 37804788
Thank you! last thing I'm worry about is that as far as leveling I might end up to 7~8 levels.
and in the link http://en.wikipedia.org/wiki/Nested_set_model#Drawbacks the drawbacks is that adding records ... now, I'm afraid in having 8 levels even modification of a node and its children or deleting would have a significant effect.

Do you think inserting several thousands dummy records and testing it will be enough?

Thanks.
0
 

Author Closing Comment

by:akohan
ID: 37816745
Thanks.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37816880
<<Do you think inserting several thousands dummy records and testing it will be enough?>>
Testing above specifications is always a good idea.  But Nested Set is usually very performant if you do not have constant updates to the hierarchies.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

22 Experts available now in Live!

Get 1:1 Help Now