Best practices when dealing with uniform SQL tables

Questions / Help
,
1

Hi and happy new year to all of you :slight_smile:

I am currently working on some microservices, that configure ISP services and reached the following “problem” for a seemingly simple task:

A radius server has multiple tables with the same MySQL data structure (id(PK, auto_increment), username, attribute, operator and attribute_value). Some are for user authentication, others are for authorization and connection setup. The radius sql plugin defines simple, single table queries (by design to handle >10.000 logins and parallel accountings as fast and efficient as possible when an ISP uses NDB)

As I’m (quite) new to Elixir, I’m considering the most elegant way to implement this for 4-6 table schemas (at the moment).

The easiest way (4-6 modules) feels a bit clunky, so I thought, there must be a better way.

My next idea was to use an embedded_schema in a macro and/or define the CRUD functions as callbacks, but I’m bound to foreign keys then, if I got the ecto documentation right.

Currently I consider moving all in one ParameterEntry module and use the {struct, source: “”} notation and Ecto.put_meta/2 for table operations. This would be more DRY, and looks cleaner at first glance, but then there may also be a lot of hardcoding table names in calls.

As I use this (admittedly, quite ambitious) project not only to make my employer happy, but also as a “real life” learning source, I’d be glad, if someone could give me a nudge in the right direction, if there is a better way.

2

Why is it that way? This feels a lot more like accidental similarity than a place to create an abstraction. What are the options for change here? Are those tables ensured to always change at once if there would be change? If yes you might look at abstract tables, if not I’d opt for skipping the abstraction and keep the separate modules.

3

Seeing it this way (from an application point of view), I think you are completely right, as i get often confused with the ops side of things :wink:

The radius POV is:
Depending on the access device one of (currently) two tables gets queried. All records for a username are selected and crafted into a user/password entry. After login, also depending on device, one of two tables gets queried for all connection parameters (traffic limits, fixed ip entries and so on). The param sets differ between the two BRAS types to some extent, thus multiple tables. These are crafted into a single radius reply. To the radius config parser all entries are treated equal: "Build a record for username for the calling module and add lines with ‘attribute’ operator ‘value’.

The microservice POV:
We get a changeset of settings by AMQP. On creation or deletion, we edit all tables at once (i.e. both check tables and both reply tables). When updating, password-changes will trigger an update of all check tables, all other an update of all radreply tables. The service creates the attribute/value pairs for every BRAS type according to the users product settings. If we should implement an incompatible access router, which I hope never happens again ;), there would be a third table to each use case.

So, to our microservice, there are more use cases, which have two different tables each, and an accidental similarity between the two types.

4

What made me think about skipping the separate modules was, that all those schema/changeset modules are implemented nearly the same, besides their table name, so a lot code duplication.

The business logic, that creates the entry sets which entries are written, is defined in a different module.

5

For completeness, this is, how I did things now:

I created a module ParameterEntry, which yields callbacks, a use macro delegating entry related functions back to the ParameterEntry module and the local functions dealing with the db persistence. The modules for the different entries use this module and implement the behaviour. So, the Reply and Check modules only deal with the business logic, and create a nested map of all related settings, that is persisted by the functions in the universal module.

If we should decide to implement, for example, group checks and replies, this is easily reusable/extendable.

1 Like