+1, and if you want to run analytics just pipe these data through an ETL/ELT process and run aggregations on another data warehouse with designs suited for OLAP, so having more tables on OLTP database is not really a problem - just need some schemas and a good naming practice
Some of these approchaes are implemented in some ORMs, like e.g. SQLAlchemy which supports three forms of inheritance: - single table inheritance - several types of classes are represented by a single table; - concrete table inheritance - each type of class is represented by independent tables; - joined table inheritance - the class hierarchy is broken up among dependent tables. Each class represented by its own table that only includes those attributes local to that class. Additionally, Django supports what they call "Generic Foreign Keys" and corresponds to your last solution.
Similar challenge earlier this year. Used technique virtually the same as option 3. Would have saved time and several brain cells if I had seen this first!
I would like choose option1 then add simple checking constraint without going write store procedure to meet the requirment becuase storing comment in one place it is helpfull for reply and mentioning feature to incorporate it at one place and for performance optimization we can use view table
Thanks for sharing what you would do, that's good to know. I'm not sure you can add a check constraint to ensure that only one of the columns is populated, but I could be wrong.
If any of the approach stores a blank column even if it is a number then I am avoiding that. So option 1 and 2 are not for me. Option 3 is good but another thing I dont want is too much joins. So the best one for me would be Option 4.
Call me crazy but I had to use the 5th method in a project where I had attachments table related to multiple content types (more content types still undetermined while developing the app). In the attachment table I added a reference_type and a reference_id Thanks Ben!
I saied option 4 even before you display it caue it will reduce search time if you fetch only one of two type even maybe all types and offers independency
+1 on option 4; less headache in the long run
Thanks for sharing!
+1, and if you want to run analytics just pipe these data through an ETL/ELT process and run aggregations on another data warehouse with designs suited for OLAP, so having more tables on OLTP database is not really a problem - just need some schemas and a good naming practice
Some of these approchaes are implemented in some ORMs, like e.g. SQLAlchemy which supports three forms of inheritance:
- single table inheritance - several types of classes are represented by a single table;
- concrete table inheritance - each type of class is represented by independent tables;
- joined table inheritance - the class hierarchy is broken up among dependent tables. Each class represented by its own table that only includes those attributes local to that class.
Additionally, Django supports what they call "Generic Foreign Keys" and corresponds to your last solution.
That's good to know how ORMs handle it. I'm not surprised - application code can handle this much easier than databases.
Similar challenge earlier this year. Used technique virtually the same as option 3. Would have saved time and several brain cells if I had seen this first!
I would like choose option1 then add simple checking constraint without going write store procedure to meet the requirment becuase storing comment in one place it is helpfull for reply and mentioning feature to incorporate it at one place and for performance optimization we can use view table
Thanks for sharing what you would do, that's good to know. I'm not sure you can add a check constraint to ensure that only one of the columns is populated, but I could be wrong.
You absolute can (ensure it via a check constraints) and it’s the most sane way to do it.
If any of the approach stores a blank column even if it is a number then I am avoiding that. So option 1 and 2 are not for me. Option 3 is good but another thing I dont want is too much joins. So the best one for me would be Option 4.
Call me crazy but I had to use the 5th method in a project where I had attachments table related to multiple content types (more content types still undetermined while developing the app).
In the attachment table I added a reference_type and a reference_id
Thanks Ben!
Good to know it worked for you!
@@DatabaseStar Well, I wasn't fully satisfied but yes it fulfilled the task
In software engineering, it's always KISS - Keep It Simply Stupid designs that are mostly efficient and easier to maintain.
I saied option 4 even before you display it caue it will reduce search time if you fetch only one of two type even maybe all types and offers independency
Amazing video
But What about Morph way?
I think its the best in this case
What do you mean by "morph way"?
@@DatabaseStar i think they refer to laravel's morph types
Option 5 is the polymorphic method.
Should an SQL beginner even look at this video?
From the commentator's profile photos, this is the stuff of legends, I reckon some of them wrote SQL 1.0 😅😂😂😂