How to manage customer-specific / user-defined attributes?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

How to manage customer-specific / user-defined attributes?

Alex Becker
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@...
Reply | Threaded
Open this post in threaded view
|

Re: How to manage customer-specific / user-defined attributes?

David Ihnen-2
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


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 <[hidden email]> 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@...



--
David Ihnen
Voice contact (562) 743-1807

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: How to manage customer-specific / user-defined attributes?

Charlie Garrison
Good morning,

Wow, great reply from David. I'm not adding much to what David
has already said so well. But I've listed a couple of packages
that will be helpful.

On 27/9/15 at 9:55 AM -0400, David Ihnen <[hidden email]> wrote:

>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.

About 18 months ago I got serious about automating schema (&
data) changes in app databases. It was semi-automated
previously, and it didn't save me any time. The edge cases were
*harder* to fix after partial automated process failed, than if
I had just done all updates manually.

There are a few tools to help with automating changes to your
app's database:

https://metacpan.org/pod/DBIx::Class::DeploymentHandler
https://metacpan.org/pod/DBIx::Class::Fixtures

But it was DBIx::Class::Migration which fixed the problems with
my edge cases, and automated schema updates are now (pretty
much) 100% reliable.

https://metacpan.org/pod/DBIx::Class::Migration

I wouldn't say the above modules are an easy learning curve to
get fully implemented (& automated), but once integrated into
your development flow, the benefit is *fantastic*.

It's a bit of a stretch, but I think of the schema management
using the above tools, like I think about git and code repos.
All the gnarly details are hidden, I just prepare db/schema/data
updates (commit changes) and then *easily* upgrade db for designers/staging/production/etc.


Charlie

--
    Charlie Garrison  <[hidden email]>
    github.com/cngarrison   metacpan.org/author/CNG

O< ascii ribbon campaign - stop html mail
http://www.ietf.org/rfc/rfc1855.txt


_______________________________________________
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@...