Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm developing a tool that handles (electrical) parts. The parts can be created, viewed, modified, deleted, grouped and so on...

In order to make this question useful for future visitors I like to keep this question universal since managing parts in a DB is very common no matter what parts are in the DB (CDs, cars, food, students, ...).

I am thinking of 3 different DB designs:

  1. Using a parts table and derived tables for specialized part attributes.

    Parts      (id, part_type_id, name)
    PartTypes  (id, name)
    Wires      (id, part_id, lenght, diameter, material)
    Contacts   (id, part_id, description, picture)
    
  2. Using only specialized part tables.

    Wires      (id, name, lenght, diameter, material)
    Contacts   (id, name, description, picture)
    
  3. Using a Parts-, PartTypes-, ValueTypes- and PartValues table that contain all values.

    PartTypes  (id, name)
    ValueTypes (id, part_type_id, name)
    Parts      (id, part_type_id, name)
    PartValues (part_id, value_type_id, value)
    

Which one to prefer and why? Or is there a better one?
I am concerned about the DB queries. I don't want the queries become overly slow or complicated.

Update

The number of types in the DB are pretty much given and static since they rest on a international standard and will be enhanced seldomly.

share|improve this question
    
Is this about strictly SQL DBs (purely relational) or NOSQL DB is also an option? –  c-smile Dec 30 '12 at 16:59
    
@c-smile: Since I haven't worked with NOSQL yet, I don't really know if it is an option. I am open to everything. –  juergen d Dec 30 '12 at 17:20
add comment

7 Answers

I go option 3

Option 1 is bad because you don't want your joins to be based off a filed value. ( ie If type ="Wire" join to TblWire)

Option 2 is bad because you have no way of reporting on your inventory as a whole

share|improve this answer
    
Also note, option 3 has the best maintenance characteristics for new part attributes, I refer to this form (though I'm sure there's a common term among DBAs for that structure which I'm missing) as a pivotted form because it is a pivot of the more common structure you detailed in #1 and #2, and often times people create #1 only to end up adding new tables/columns for new types so often they have to pivot to #3 after they've made a huge mess they can no longer maintain. –  Jimmy Hoffa Dec 27 '12 at 17:16
    
For option 1, you would never need an "if" on the type before a join. If it joins successfully, then it is the type. Joins themselves could replace filters. You could go so far as to no longer store the type. –  mike30 Dec 27 '12 at 18:42
    
@mike what if he want 2 product types? If cable join to "Cables", if connectors join to "connectors", if he joins to both he gets nothing! If he left joins he gets Duplicates! –  Morons Dec 27 '12 at 20:18
    
@Morons. Left join the master with the sub-tables. Filter where calbles.ID is not null and connectors.ID is not null. Viola! Using the success of the join as the filter. –  mike30 Dec 27 '12 at 21:26
2  
@Morons: repeating the word "nightmare" does not make it more true. If one has to modify "all the code" when a new type is created has nothing to do with "option 1" or "option 3". It has to do how well the code is structured. And that one has to modify code in some places when a new requirement arrives is not "a nightmare", that is just normal (and necessary also for option 3). Before arguing any further, I suggest you inform yourself about the cases the Entity-Attribute-Value pattern is appropriate, and when not. EAV is sometimes an anti-pattern. –  Doc Brown Jan 2 '13 at 9:37
show 7 more comments

Option 3 is more generic and can accommodate more use cases.

Going option 3 you may need more joins and complex queries for simple features, in option 2 you'd need complex queries for "big" features like inventory and reports, and may need use unions to accomplish that.

You can always simplify you queries in options 3 using Views, if you very often needs only the Wire or Contact, make a View for each of them. You can optimize it if it becomes necessary.

share|improve this answer
add comment

I would tend to not option #3.

Option #3 is name-value pair setup violating normalization.

Ideally, one attempts to have some level of normalization of the database. Strive for complete normalization and then denormalize as necessary when it is identified for customization or performance issues.

Consider the query "what are the name and part IDs for all wires made of copper"

Structure #1 is

select
  name, parts.id
from
  wire, parts
where
  wire.material = 'copper'
  and wire.part_id = parts.id

Structure #2 is

select id, name from wire where material = 'copper'

Structure #3 is

select
  parts.name,
  parts.id,
from
  parts, part_types, part_values, value_types
where
  part_types.name = "wire"
  and parts.part_type_id = part_types.id
  and value_types.name = "material"
  and value_types.id = part_values.type_value_id
  and part_values.value = "copper"

Consider also the complication of inserts and deletes from the system.

Some further reading on why not #3 -- The curse of the name value pair

share|improve this answer
    
+1 Nice article you referenced there. –  juergen d Dec 27 '12 at 18:09
2  
Yes the name value pair is evil I think all agree, but it continues because it is a necessary evil. Perhaps #3 is unnecessary here, but it appears a great deal like the table structures I've seen become untennable and ended up needing denormalization to the name value pair form. If it is however fixed then perhaps #1 is the right approach (assuming queries would want to act upon aggregates of different parts, otherwise #2 is fine) –  Jimmy Hoffa Dec 27 '12 at 19:58
    
Also you're not using joins here, which ends up putting undue work into the where clause that would go into the join like the part_type_id = part_types.id and value_types.id = part_values.type_value_id are both join clauses leaving the where to where part type is wire, value type is material and value is copper which is relatively succinct –  Jimmy Hoffa Dec 27 '12 at 20:01
    
@JimmyHoffa I was just doing a quick abbreviated version to show what it would look like rather than ideal sql. The third option I have seen in Redmine's table structure where name/value pairs are added to the system on the fly. Having to do database updates to add a new custom field is impractical - so name value is the appropriate structure. However, it makes database queries a bit slower (indexes are not as happy as the type becomes strings for everything) and queries a bit ugly. –  MichaelT Dec 27 '12 at 21:09
1  
Last time I did option #3 it was in MSSQL and I used the SQL_Variant type, I believe indexes like that a little more than strings because it catalogs them by type then value if I'm not mistaken, though still it is a more complex approach and as you said it's best when you know there will be a consistent growth of new types, last time I did this it was converting a table with 60 columns; 1 for each key that consistently grew so these scenarios obviously happen but perhaps this isn't one of them, that would be up to the OP to identify. –  Jimmy Hoffa Dec 27 '12 at 21:28
add comment

Option 3: (sometimes)
Option 3 is the "EAV" design. In theory it is nice because the fields are taken out of the table structure and become data. But it gives terrible performance. It also disallows the use of proper indexing. And it makes queries much more complicated.

I would only use EAV in special circumstances. I have used EAV to calculate auxiliary parts needed for orders and it worked well. But be very weary of using it as the design for your core tables.

Option 2: (never?)
Option 2 is a no no. What about the shared fields? Are you going to duplicate the table structure for every shared field? It would require you to include unions in reports of the entire system.

Option 1: (winner!)
Option 1 may seem a little too basic but it is probably the best bet for your core tables. All the parts use the same master table for shared fields so it avoids unions in your reports. It has great performance allowing the proper use of indexing. Queries are in the traditional style and are simple.

The downside of option 1 is you can't add fields dynamically. But do you really want to? By dynamically adding fields you are performing database design at run-time.

share|improve this answer
    
+1, but have a look at my answer to see what the rationale behind option #2 may be. –  Doc Brown Dec 30 '12 at 20:42
    
After some thought and based on the notes from the OP that the parts are an absolute fixed standard per regulation, I agree to Option #1 and +1 for the good answer, though he should definitely keep in mind Option #3 may be a migration point in the future, also important because nobody else mentioned it: Outer joins have poor performance characteristics in general and should be avoided where possible Just adding that because Option #1 will involve outer joins, but in this case is still probably worth the cost as Option #3 has it's own performance pitfalls. –  Jimmy Hoffa Jan 2 '13 at 16:04
2  
Option 1 may seem too basic? No way, that's definitely the way to do it. Jimmy is wrong, outer joins do not have poor performance characteristics in general. As long as you index properly it'll be fine. –  LachlanB Jan 4 '13 at 3:58
add comment

With NOSQL DB database (like MongoDB for example) you will just need one set named "Parts". Each part in that set is so called document - record with variable set of fields:

{
   "_id": ObjectId("4efa8d2b7d284dea1"),
   "partType": "wire",
   "length": 102.5,
   "diameter": 1.5,
   "material": "silver"
}, 
{
   "_id": ObjectId("4efa8d2b7d284sjsq23d"),
   "partType": "contact",
   "description": "something",
   "picture": Binary(...)
}, 

I think that this is the most natural data storage for the task you describe.

share|improve this answer
add comment

I would start with a data/object model allowing inheritance, and then use a standard object-relational mapping. This way you get a base class Parts and sub-classes like Wires, Contacts etc. Now, if you apply a "map-each-class-to-own-table" strategy, you get option 1, which is the most "normalized" solution and should be the canonical strategy if you don't have any more information about the queries you expect.

Option 2 is what you get when applying a "map-each-concrete-class-to-own-table" approach. This can avoid "joins" and may perform better for some kind if queries (especially queries for just one "part type"), on the other hand it makes generic handling with all parts harder and slower. Avoid this if you don't have any special reasons for it.

Option 3 is what you need only if you want the user to change the number of part types at run time - if you don't expect that requirement, option 3 will be a perfect example for over-engineering things.

share|improve this answer
add comment

Definitely go with option 1 but with a few very simple modifications:

Parts      (id, part_type_id, name)
PartTypes  (id, name)
Wires      (id, part_id, part_type_id, lenght, diameter, material)
Contacts   (id, part_id, part_type_id, description, picture)

You can then use CHECK constraints and DEFAULT values to ensure that the part_type_id is correct, and then you can join on both part_type_id and part_id. This avoids having a conditional join based on only one table, and if you need to add a part_type_id to wires (say we are subdividing that part and adding another table of extended attributes) the default and check constraints can be changed.

share|improve this answer
    
You can also (safely - unless some ORM requires single-column primary keys) remove the wires.id and contacts.id as the (part_id, part_type_id) combination will be enough for uniquely identifying a part. –  ypercube Feb 19 '13 at 7:08
    
@ypercube, sure, but since part_id is unique in this case, just use it as the primary key, with a secondary unique index on part_id, part_type_id if you want. –  Chris Travers Feb 19 '13 at 7:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.