Search code examples
design-patternsbusiness-logicarchitecture

How shoud be a parameters table designed and used on businesses and GUI layers?

I'm currently designing an application and I need to know how should be designed a constants parameter table. Also, I want to know how to use this table on the application.

For example: for a parameter sex (M or F) in a system, it should be in its own table or is better to have that parameter with others in a special table?. How can I "envelope" parameters in the subsequent layers (using a constants class with the parameters and their primary keys)?

I have heard about a special "DB pattern" or a common way to create a table such that its schema lets the development process to relay on this single parameter table. Do you know how's this called?

Also... could you recommend me any good bibliography on the issue?

Solution

  • I've built and used many times what I call Parameter Enumeration Tables. The module is part of my ZXAF opensource framework.

    Basic design is simple, you have a Parameters table that has a 1-many relationship with each table that needs a parametrised field. It looks something like this:

    alt text

    Expanding on this to provide a real example, where we are working with a users table that contains a status field. We index and link the field to the params table via a constraint as follows;

    INDEX `FK_user_status` (`status`),
CONSTRAINT `FK_user_status` FOREIGN KEY (`status`) REFERENCES `params` (`id`) 
           ON UPDATE CASCADE ON DELETE CASCADE

    NOTE: I'm using CASCADE here, there are times when you don't want to do this

    This gives us the following schema;

    alt text

    The key concept of this is to allow the database to contain parameterised data that maintains referential integrity, and integrates with a data model within the code. The code is able to find out by querying the database how entities are related, and for example what the valid values for a specific field are.

    Parameter Enumeration Tables sample schema

    Lastly I want to introduce and explain the concept of Parameters Tuples. This is another table that allows us to associate a pair of parameters (the Tuple) with a value. This a data neutral way in which we can extend PET provide the lookup and expected values. This is most suited to an extensible model where it is possible to add new enumerations, and yet we need to allow them to contain a value. It is often better to do this with *relationships*

    Parameters schema with Tuples

    I'm not in favour of enums in databases, but this is only my opinion and it may be something that you're happy with.