Table Inheritance and Abstraction Layers

[kd2017]

I'm curious to know if anyone here is ever implemented inheritance concepts in a ms access db design before?



How'd it work out? More trouble than it's worth?

Did you create an abstraction layer with queries between the tables and UI?

Generally speaking is an abstraction layer a good thing or a bad thing for maintainability?

I'm playing around with this idea right now and while it's neat it's one of those things that I can tell I'm probably going to have no idea what the hell I was thinking 6 months from now...

[isladogs]

Sorry to say I have no idea about what you are thinking now
Perhaps you can dumb this down a little to make it easier to understand!

[kd2017]

Refer to this documentation for python's sqlalchemy orm library on the subject: https://docs.sqlalchemy.org/en/20/orm/inheritance.html

What I mean be table inheritance is like class inheritance in object oriented programming. Given a row in a table represents the properties of an object, what if you have an object that's basically the same but needs a few more properties to be fully represented?

For example you could have a table called [animal] which has a few basic fields for properties that all animals have, then you could have a table called [tiger] that is 1-to-1 with the animal table, so it "inherits" animal base properties and adds fields for specific to a tiger's properties. You could have another one for [octopus], [bird], etc. This is the 'joined table inheritance' mentioned in the docs linked above.

What I mean by an abstraction layer in queries is you have a queries called [qry_tiger], [qry_octopus], etc that pulls all the "inherited" tables together and then all of your forms/UI are based off of these queries.

Maybe you have a denormalized flat table that has fields for all the properties of the types of [animals] that you might need, ie "single table inheritance", and you create an abstraction layer of queries [qry_tiger], [qry_octopus], etc. that breaks the relevant fields out and you build forms specific to tigers or octopi and so on.

[orange]

Perhaps this link has info related to your post.
You may also find something in this thread.
Or here

[NoellaG]

The data layer answers to different rules than the object layer. You need to design the data layer for fast query execution. In your case this could be a table 'Animals' with all fields for all animals, a table AnimalTypes with values as 'Tiger', 'Wolf', a table AnimalProperties with the fields ID, animaltype, and Property, and a table AnimaPropertyValues with the fields Animal (=ID from table Animals), AnimalProperty (=ID from table AnimalProperty) and PropertyValue (= the value for that animal for that property).
Above that layer you can create a query layer and class modules with a class for each animal type.

[kd2017]

So for the sake of speed and efficiency would you say you might favor designing more obscure or abstract table structures/relationships at the expense of clarity?

[NoellaG]

The more obscure/abstract normalized structure is mostly to ensure correct data retrieval, not running into trouble when creating queries and preventing redundant data.