Ortix Posted May 13, 2014 Share Posted May 13, 2014 (edited) This is a more general schema design question as opposed to specific queries. I'm designing a database which tracks the production status of 2 (and in the future maybe 2 or so more) completely different products. So let's call these products productA and productB. I have a page where the user can see the progress of either of the products. This concerns these 3 tables: productA(id, order_id, status) productB(id, order_id, status) production_status(id, status, ordering, type) The status is number based PER product so that it can move up a chain of statusses in its production process. So if a product is in status with ordering 10 it's done (assuming there are 10 production steps). So at ordering 1 its production just started. The status field contains at which production step it is which will be visible on the page. Now this is where i get stuck. I somehow have to differentiate between statusses so I know which statusses belong to which product. I put type in so it could filter for either productA or productB, but also for future products. But working with strings is not such a smart idea I think. I could make 2 more seperate tables, but I'm not sure how well that would scale. So my question is what a good approach would be. Some background info: I'm building a Joomla component for a small company. Like I said they want to track the status of these products. Every time a production person unpublishes said item on its production view inside the component, the product moves to the next status Edited May 13, 2014 by Ortix Quote Link to comment https://forums.phpfreaks.com/topic/288458-database-schema-design-question/ Share on other sites More sharing options...
Barand Posted May 13, 2014 Share Posted May 13, 2014 Do both/all products go through the same production steps? Quote Link to comment https://forums.phpfreaks.com/topic/288458-database-schema-design-question/#findComment-1479326 Share on other sites More sharing options...
Ortix Posted May 13, 2014 Author Share Posted May 13, 2014 (edited) Do both/all products go through the same production steps? Forgot to mention, but no they go through completely different steps so productA can have xyz steps and productB can have uvw steps. Edited May 13, 2014 by Ortix Quote Link to comment https://forums.phpfreaks.com/topic/288458-database-schema-design-question/#findComment-1479331 Share on other sites More sharing options...
Solution Barand Posted May 13, 2014 Solution Share Posted May 13, 2014 Probably want something like this Quote Link to comment https://forums.phpfreaks.com/topic/288458-database-schema-design-question/#findComment-1479333 Share on other sites More sharing options...
Ortix Posted May 13, 2014 Author Share Posted May 13, 2014 Thank you! very simple! I figured it would be something like that. Thanks for confirming Quote Link to comment https://forums.phpfreaks.com/topic/288458-database-schema-design-question/#findComment-1479334 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.