Which Database Model?

I've primarily implemented relational DBMS, but I don't think it is suited to the following...I have set of up to 10^4 of relatively complex 'objects' that are composed of 'parts' numbering up to 10^2. The parts are assembled into 'assemblies'. Objects have operations performed on them to establish patterns among both parts and assemblies and are compared with patterns present in other objects in the database.

Using RDBMS, I could simply aggregate the 'parts' into Table B, which have very little out of context use (unlike an item number on an invoice for example) and link them to their parent 'objects' in Table 'A' or to 'assemblies' in Table C. However, I would have to run a SQL SELECT on this entire table just to 'collate' the objects, which are really just a serial subset within Table 'B'.

Alternatively, these could all be in one table with a ParentID field; the absence of which indicates 'object', not 'assembly' or 'part'.

I'm thinking VIEWS could be set up for each object to 'index' the table as it wer开发者_Go百科e, however. I don't know if it feasible or best practice to use 10,000 views!

Is there another model I should learn that may organize the data in a more conducive way? The data would be a set of heirarchal objects composed of 'assemblies' of other 'assemblies' or atomic 'parts'.

Can someone point me in the right direction?

---

If I'm reading your description of your object model correctly, it sounds as if:

• 1-many parts are in an object
• 1-many parts are in an assembly

It sounds as if you were originally thinking something like this:

Assembly
  ID int

Object
  ID int

Part
  ID int

ObjectPart
   ObjectID 
   PartID

AssemblyPart
    AssemblyID
    PartID

You'd then be able to find the parts for a given assembly:

 SELECT P.* 
 FROM Parts AS P 
 INNER JOIN AssemblyPart AS AP ON AP.PartID = P.ID
 WHERE AP.AssemblyID = @SomeAssemblyID

I'd suggest keeping distinct and separate entities, and avoid the ParentID field that would dictate the type of entity each row is. I'd agree with you that a View for each entity wouldn't be a good/maintainable way to go. You'd want to SELECT from the View with a WHERE clause on the AssemblyID or AssemblyName.

Perhaps post a quick relational schema that you think would give you problems, and a description of your anticipated problems?

---

Network Model with RAIMA or Heirarchal with IBM IMS are the only options (proprietary) available. So it is either XML like Zope or using methods online to implement Network Model using RDBMS.

---

To add to Stephen's answer, I work for Raima and one of our engineers wrote a very thorough post on the Network Database Model, how it works and the differences vs. the Relational Model.