CODEWITHSUNDEEP
databasearchitecture
Blackbeard
Blackbeard

Reputation: 662

MySQL database architecture - multiple types of interdependent items

I am designing a web application. The logic of the application is basically a wizard, where when you make some choices in previous steps, it affects the possibility to choose items on next steps.

Usecase:

Step 1 - User chooses to use item1 and item2

Step 2 - User have a choice to use item3 or item4, dependent of the choices in step1

Step 3 - User have a choice to use item5 or item6 or item7 or item8, dependent of the choices in step1 AND step2

All depict items represent different products with multiple properties. The total number of different items may reach hundreds and each item could be dependable on more than one other item.

I would like to get an advice about the architecture of a database.

Aim: describe in the database how itemX requires itemY (and possibly itemZ, itemN, ...)

I am wondering how could those dependencies be implemented in the database, because having a thousands of lines of 'switch's and 'if's in the application code is just not right.

Thanks.

Upvotes: 1

Views: 554

Answers (2)

Jawad Al Shaikh
Jawad Al Shaikh

Reputation: 2735

with all respect, your question should be called how i analyze/solve problems! a moment you think in UI and other in design and mixing the talk about item/itemType! random thoughts [bad brainstorming] will increase the time to solve the problem.

think of one thing at a time then by time you'll be more capable in processing varying inputs based on focus and experience!

First: based on what, you assumed that each itemType require table/class? since the entity called itemType then all items must belong to the STRUCTURE of itemType, hence at least by [itemTypeId] correct!

Second: you got to choose which easier to make you understand/breakdown the problem [DB design or UI] but not both at the first glance. usually it's much easy to go with DB design first if its well-known problem otherwise it'll be good to start prototype ui with focus on ui only,

so please edit question and think more to get more!

good luck!

EDIT:
after question edit, i understand that the problem belong to [workflow design]. with workflow you can go dynamic or static. dynamic require solid and deep knowledge in connecting nodes and implementing them, on the other side "static" one is easy to implement but require deep analysis of the problem to breakdown every and each little detail, your brain can think of! for dynamic workflow, I strongly recommend to study ms-sharepoint implementation since it cover that totally and its database design can be viewed. [i recall that they had i huge table in sharepoint service 3.0 that contain more than 100 columns which work like the base table i think or maybe for auditing!] in other words you will be required to add/remove columns to tables while the application in runtime and link them to the application logic/workflow process. this is main difficulty source of the dynamic approach. on large scale only few software packages 'ERPs' like SAP, SAGE, MS-Dynamics Oracle Siebel crm managed to handle that. hope that's was helpful, i can add more if needed!

Upvotes: 0

billygoat
billygoat

Reputation: 21984

One thing is for sure. You need to store the association in a seperate Join table. Lets call this product_dependency_tb. In a normal case, (like say questionnaire management) all the dependencies will be of same type and hence for each entry, there will be a list of dependent items. But since you also need to store additional metadata for table name, here is what I think is a decent solution.

structure of the Join (assoc) table:

 product_id --- Enabled_product_Table --- Enabled_Product_Id

 Itemx          Table1                 ItemY
  .             Table2                 ItemZ

Now when you query for dependent records, of course you need some additional logic in your application tier( or you can write this as a procedure).. (more precisely, you need to run the select query in iterative mode to get all the dependent products).

If you are worried about performance, then you could also group enabled_product_ids of a particular table together. (as a comma seperated string, so that you can perform an in condition)...

Upvotes: 1

Related Questions