[Dbix-class] How to manage customer-specific / user-defined attributes?

[David Ihnen]

Create a procedure in your code base operations for the definition of
customer specific data model classes that work through some kind of
pluggability to operate the application in an instance mode that has those
models.  Your operations will also do the creation of tables as relevant in
appropriate databases.  Keeping control if this is somewhat tricky, is
really best done with LOTS of automation so that there aren't any manual
sql processes to process if at all possible.  My advice is to put as much
effort as necessary into the operational development in order to enable the
automatic management of the back end storage and the classes for the
application instances.

But to discuss the issue in general in aspect to horiz/vert table design
(which is the usual customer-attribute problem, you probably didn't know
the term):

http://stackoverflow.com/questions/9256830/horizontal-vs-vertical-table-design-sql

which references

https://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/

Which is all information you probably need to know to make an effective
data model, read more books and stuff on it.

As a corrolary to my first paragraph, I want to say that the data model of
an application is defined by the application itself. The data model of the
indexing back end storage whose purpose is the retrieval of information in
an adequately performant manner for the operation of the system is
derivative and often closely coupled to the application. The less that you
have to maintain the backend store manually in relation to the application
model, the more maintainable your system will be. The usual tangle of
database schemata evolution against the application evolution through its
versions is for the most part avoided.

So to be precise, it depends on the use cases of using those attributes how
you should render them in the database/dbix class definitions - the simple
sideways table isn't performant, the operationally heavy
new-tables-and-columns-for is somewhat perilous and increases ops load.
But you got to do what you go to do given the constraints of the system and
environment, right?

:D

David



On Sun, Sep 27, 2015 at 5:33 AM, Alex Becker <asb.cpan at gmail.com> wrote:

> Dear all!
>
> Whenever I start setting up a somewhat larger database, I come across the
> question: how to design customer-specific attributes?
> I can't imagine that this question was not elaborated extensively in the
> Internet, but I did not manage to find a good article about it. Maybe it's
> something trivial.
>
> Example:
> Assume a generic web shop. You have a table of products. These products to
> have a set of pre-defined attributes like price, VAT, weight etc.
>
> Some time later, new attributes will be added. Simple new, single-value
> attributes, as well as multi-value-attributes.
> Like colors (can be either blue or yellow) or topics (1 or more of:
> gardening, cars, programming, ...).
>
> Do you know a comprehensive guide how to do it best? In the ideal case, by
> using DBIx::Class :)
>
> In the past, I used hand-make code to alter tables (add or remove columns,
> while keeping the default columns), and the normalization approach with
> single values attributes.
>
> Best regards,
> Alex
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- 
David Ihnen
Voice contact (562) 743-1807
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150927/e4db86db/attachment.htm>