Timeline for Solutions for Introducing a new Entity In Between An Existing Association
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 11, 2019 at 17:11 | comment | added | karns | Well, nothing really. I suppose a companies name perhaps, projects name (within company), etc. However, the primary keys (auto inc) are really the way I'd like to identify the records. With that said, it shouldn't change the substance of the question, I wouldn't think. | |
| Oct 11, 2019 at 15:50 | comment | added | SQLRaptor | sorry, I missed that last entity - you have 4 levels in the hierarchy, not 3 - Companies -> Projects -> Orders -> Batches. What are the identifying attribute of each of these entities? I mean the business keys - what tells them apart in the real world? If you provide these I'll answer with a full schema as I see it. | |
| Oct 11, 2019 at 12:16 | comment | added | karns | So with that said, you'd suggest NOT adding Company as a parent to Orders? More specifically, adding company_id to the Orders table? | |
| Oct 10, 2019 at 19:06 | comment | added | SQLRaptor | I wasn't talking about the schema, I was talking about the data model which comes first. As the data model suggests 3 entities - Projects, Orders, and Batches. A project may consist of of 1 or more orders, which in turn may consist of one or more batches. These should be your guide to creating these tables and relationships. Any attempt to 'get smart' and circumvent the data model relationships in any way, will result in grief and regret... I would also highly recommend that you reconsider using surrogate keys for your entities and look for the identifying attributes instead. Good luck! | |
| Oct 10, 2019 at 16:15 | comment | added | karns | Also, to clarify, are you suggesting to try to remove project_id on batches, then? Then create an order to house all old data in order to be able to derive the project/company still? | |
| Oct 10, 2019 at 15:53 | comment | added | karns | Appreciate your understanding and advice. This was sort of my gut feeling. With that said, however, what are the implications of doing something like David is suggesting? Is it considered a bad practice to have a grandchild contain a foreign key to a grandparent? | |
| Oct 5, 2019 at 17:52 | history | answered | SQLRaptor | CC BY-SA 4.0 |