I have nearly no experience in database design, so forgive me if I'm asking something super nooby and ridiculous :)
I have an item I need to create. This item will be constructed from 3-4 types of objects, with multiple object instances. Basically as per below:
- Object 1: only 1 instance per item
- Object 2: extends Object 1 and shares a foreign key, may have multiple instances, one instance mandatory
- Object 3: extends Object 2 and shares a foreign key, may have multiple instances, one instance mandatory . There are 3 types of Object 3 which are dependent on a field from Object 2 (haven't yet figured out how these need to link without code).
- Object 4: extends Object 3 and shares a foreign key, may have multiple instances, not mandatory, so may be omitted.
Is there a way to dynamically generate Object 3 through MySQL? Like if field #3 shows Object 3.1 call that table, if it shows 3.2 call that instead. Similar to a constructor.
Also is there a way for me to map related objects? For example, since everything is dependent on Object 1, I'd like to be able to call Object 1 and have it pull all related instance of Objects 2, 3, and 4 related to that specific object. I was thinking of creating a separate table to hold primary keys of related objects, but I'm thinking there is probably a better way of doing that.
Also I've read that I shouldn't use stored procedures, and instead do everything with code. Is that correct, because I would think for my model it would be more convenient and cleaner to just call on Object one, and have MySQL do all the joins internally and return a single completed object
Thank you very much for your help.