I'm currently working a project where I'd like to create a small application to compute spare parts criticality (for the maintenance department). Something based on Excel files already exist but isn't fully automated nor multi-users. I thought I could rely on Sharepoint lists and workflows to build something better. However, after reading quite a few tutorials / msdn docs, I still struggle to get things done.
In my application, there are basically 4 entities: Sines, Machines, Functions, Parts. Sites contains machines and machines have functions (one to many relationship). Functions require parts to work (many to many relationship): Site(0,N)---(1,1)Machine(1,N)---(1,1)Function(1,N)---(1,N)Parts
In order to store information about these entities, I thought about creating 5 lists: one for each entity, and a part - function association list.
Each entity has its own criticality. Machines, sites and functions criticality can be computed based on their own properties. This can be done easily using a sharepoint calculated field. However, parts criticality depends directly on the criticality of the most critical function it belongs to and on the criticality of the machine that has this function: Machines criticality + function criticality => part criticality. This is where things get difficult for me: I cannot find an easy way to do that. If I was using an SQL db, I could more easily get the function and machines criticality (with joins), but can I do that within a workflow using CAML ? Also, items' criticality should be computed again each time a user update / add information.
Is it a good idea to use Sharepoint for this kind of application ? If so, can you help me with this ?
Thanks a lot, Sébastien.