How to implement inheritance?

[boogerlad]

Typically with SQL databases, to implement object oriented inheritance, three methods are used.

1. make a table with enough columns to encompass all possible types. Many rows will have nulls for columns that are not applicable to its type, so this method won't work with Project:M36
2. make a table for each type
3. make a table for common attributes, and a table for each type for the remaining dissimilar attributes

Can we do better than option 2 and 3? "relational proponents are less likely to believe in the utility of hierarchical taxonomies and sub-typing because they tend to view set-based taxonomies or classification systems as more powerful and flexible than trees" from wikipedia

Specifically, if I'm trying to sell CPUs, monitors, food, etc, the only common attribute between them all would be name, price, description. The rest of the attributes might look like

CPU

• clock speed
• cores
• cache

monitors

• horizontal resolution
• vertical resolution
• diagonal mm

food

• calories

With option 2, I wouldn't be able to list all products without explicitly knowing all the types so that I can select * from cpu, select * from monitor, etc

With option 3, you can determine the type from querying just the main table with the common attributes, but it's a little... involved. I don't like using strings to specify the type since you can't programmatically determine which sub-table needs to be joined to retrieve the rest of the information without a dynamic query or joining every sub-table (don't need the tableoid check since constraints guarantee one to one relationship), and there are many constraints here to ensure integrity. Surely there's a better approach?

EDIT: since #310 is still open, I don't think I can implement the circular dependency between rows in the two tables

[agentm]

I would posit that Project:M36 has better support for object-relational mapping than typical SQL databases, but it's only because it supports more features of the relational algebra (so, it's not intentional). You've enumerated some of the common mappings and those designed do indeed frequently fall off the rails, as you point out.

One Project:M36-specific feature which you can leverage is algebraic data types. So, for example, you can represent the item-specific attributes using a data type like:

data ProductProperties = 
 CPUProperties ClockSpeed Cores Cache |
 MonitorProperties HorRes VerRes Diag |
 FoodProperties Calories

and plop that into a single attribute (column) in the product table. The downside is that data types like this are fixed, so, if you gain new types of products in the future, their properties cannot be represented without updating the data structure.

If you choose the EAV (entity-attribute-value) table strategy, Project:M36 can actually make it more natural to query with relation-valued attributes. Take a look:

TutorialD (master/main): product := relation{tuple{pid 1, name "CPU"}}
TutorialD (master/main): prop := relation{tuple{pid 1, key "speed", value "4"}, tuple{pid 1, key "cores", value "32"}}
TutorialD (master/main): :showexpr product join prop
┌─────────┬──────────┬────────────┬───────────┐
│key::Text│name::Text│pid::Integer│value::Text│
├─────────┼──────────┼────────────┼───────────┤
│"cores"  │"CPU"     │1           │"32"       │
│"speed"  │"CPU"     │1           │"4"        │
└─────────┴──────────┴────────────┴───────────┘
TutorialD (master/main): :showexpr (product join prop) group ({key, value} as props)
┌──────────┬────────────┬───────────────────────────────────────┐
│name::Text│pid::Integer│props::relation {key::Text,value::Text}│
├──────────┼────────────┼───────────────────────────────────────┤
│"CPU"     │1           │┌─────────┬───────────┐                │
│          │            ││key::Text│value::Text│                │
│          │            │├─────────┼───────────┤                │
│          │            ││"speed"  │"4"        │                │
│          │            ││"cores"  │"32"       │                │
│          │            │└─────────┴───────────┘                │
└──────────┴────────────┴───────────────────────────────────────┘

Unlike SQL ORMs, a Project:M36-based ORM can used a relation-valued attribute to extract all sub-attributes in a single query, even if those attributes require joins.

There is yet another option which will allow you to peg the set of attributes to specific products so that specific product type must include specific attributes. It is possible to join against another intermediate table which includes an ADT which indicates (perhaps implicitly) which attributes must be related to which product type. This can be enforced with an inclusion dependency (constraint) to ensure that a CPU, for example, always includes a speed.

In summary, you have a bunch of options depending on how static you want the attributes to be.