mercredi 15 septembre 2021

Custom Fields(Attributes) Database Design

I am trying to architect a SaaS database for a multi tenant application (i.e. all customers will have the same Database). One of the key requirements for a SaaS application having the ability by the customer to add Custom fields of multiple types (int, float, string, long, etc) to extend the SaaS application to meet their specific use case. I also need to record the history of changes as it happens on each custom field. (for design scale purpose expect 1 change per week per field) How will you design a table for this? A few options I have are: Have a Table pre defined custom fields table (say with 24/48 columns (this will the limit for custom attributes customers can define), and let them only name the label for each pre defined custom_field_1. Q: Will I need a table for each eight type? I have no way of knowing what types they will use. Have a unique custom table for each customer (will be a 1000 tables for 1000 customers, so this table won't be multi tenant. Will this put a lot of my Database? Since every customer will be using this table extensively. EAV (Entity Attribute Value) approach of creating a Row Store for every time data is entered. Q: I like this, but how do I show when the data has not be provided (null)? Do I make an entry for null when a custom attributed is created by the customer?

Aucun commentaire:

Enregistrer un commentaire