"Flat Table" structure for Different Resources


#1

The data model I want to use for my next project is a rather unusual.

I want one table for Collections, One for Items.

A collection could be books, cars, jumpers, laptops etc.

Each item would store custom attributes in one field in json called Meta.
So the items table would have a title, description, userID, createdDate etc that were generic for all items.
(and of course have a itemTypeID to distinguish between items).

A book would have author, pages, publishedDate etc stored in json in the Meta field.
A laptop might have processor, ram, screensize in the Meta field.

I still want to have the REST URL look like /api/v2/book/… and /api/v2/laptop/…
However in this case book and laptop would be the same field but filtered for itemTypeID

Is this possible? What would be the easiest way to achieve this? Should I run a script pre the action or could I just use views?

I would also want to be able to run filters based on those attributes stored in the Meta field. For example books with over 100 pages.


#2

Probably the easiest way to achieve this is with views. Each view would be called by the item type you want it to represent, so the rest paths would end up looking like /v2/api/{databasename}/_table/{view name}

You could also create a custom script. In 2.0 custom scripts are services, so their rest path looks like /v2/api/{script name}
In your script you could use platform.api to call your database with the filters you desire, so for example you create a custom script service called books (accessed at /v2/api/books )
In your script you make a call (using platform.api) to /v2/api/{database name}/_table/{table name}?filter=TypeID%3Dbook