Feature: Inherited table types / EXTENDS

lveillard commented 2 months ago

Is your feature request related to a problem?

No

Describe the solution

DEFINE TABLE Employee EXTENDS Person ....

Would have multiple impacts:

All fields are inherited, so no need to duplicate their definitions/assertions/events
When querying FROM:Person, we get as well FROM Employee (or other subtypes)
Could define abstract tables that actually don't accept any records

Non mainstream potential additional features:

Inherit from multiple tables

Alternative methods

We just duplicate everything in the schema right now to generate this effect. And queries that could go as

SELECT * FROM Person:7

look like this

SELECT * FROM Person:7, Employee:7, Intern:7 ....

SurrealDB version

2.0.0.beta3

Contact Details

No response

Is there an existing issue for this?

[X] I have searched the existing issues

Code of Conduct

[X] I agree to follow this project's Code of Conduct

Odonno commented 2 months ago

Interesting idea. This could however introduce a strong adherence with the "base" table which will need to add some kind of referential integrity.

An alternative keyword PROTOTYPE could look like this:

DEFINE PROTOTYPE people [ FOR TABLE ];
-- A prototype is a fake table as we cannot assume anything on the "inherited" tables (SCHEMAFULL, PERMISSIONS, etc...)

DEFINE FIELD first_name ON people TYPE string;
DEFINE FIELD last_name ON people TYPE string;
-- etc...

Then when creating a table like Employee that extends this type, we can keep the "reference" to the inherited tables and throw an exception when we try to REMOVE PROTOTYPE and a reference still exist.

Additionally, you could target "inherited" tables when defining the "prototype" but I am not clearly sure how that should behave at the moment. See:

DEFINE PROTOTYPE people FOR TABLE employee, intern;
-- Prevent any other table like `user` to inherit from this prototype
-- Can only remove if no reference? or no table `employee` or `intern`?
-- Note that the referenced tables do not have to exist at the time of the prototype definition

lveillard commented 2 months ago

Yes PROTOTYPE TABLE could work, equivalent to ABSTRACT TABLE. But to clarify not all inheritable tables would be abstract, we could have employee EXTENDS person and both having records, just employee table would have extra fields (like a ref to a company for instance)

Another challenge here is that tables definition could be overrided later, so if we modify person table to extend animal table, then it means our employee table records not only belong to person but also to animal, so SELECT * FROM animal:myEmploye1 should work.

It has some challenges for synchronizing but it adds more value, enhances the data modeling experience and makes the schema cleaner for complex data models. Our schema right now is really long and ugly because we need to duplicate everything

surrealdb / surrealdb