An idea for LER's valid values (chars mainly)

Toad Data Modeler
1

I’m thinking about an idea.

It arose when I decided to move to postgresql.
I had problems with my enum values. Postresql does not have such a data type.
Then I gooogled a bit and one reason was that “enumerated data types are common for bad database design”. What!?

Ok. Nevermind, in fact MySQL has to have it stored somewhere in some table.
So I created one “generic” table for constants that contains constant’s name, description, localized stuff. And one inherited table for each enumerated type I need. Then I replaced enumerated attributes with foreign keys to those inherited special tables.
The result is superb, functional, but something is missing.

In LER only valid values can be used for enumerated data types. And maybe in some future release TDM could convert valid values into ENUM fields in MySQL. ANd fot other types it supports CHECK constraints already. And a third option could be automated eneration of such tables. What for ? Creating status data fields is a part of database design. Check constraints are good, but impossible to manage/alter from application perspective once the design is set.

In Valid values tab (data type, domain) there could be a combo box with list of “constants tables” similar to categories. These tables could be managed similar to domains, categories from “model” application menu.

And for each constants table there could be defined a master table. Ofcourse it douesn’t have to be called tables there in LER. More like “constants domains” and “constants master domains”.

To further improve it, we could make out them “constant settings/schemes”.
Then the scheme would include options if we want to generate ENUM data type (in MYSQL only), or CHECK constraint (everything except mysql), or constant’s table inherited or not inherited from master constant’s table. The choice would be similar to entity inheritance in LER.

I’m creatin web applications with lots of status data types. Those types are common to be changed in the future. I don’t use groups/users/categories, but this could come in handy.

2

Hello arki,

Thanks for your email.
We have discussed your issue in our team. Here’s the result.

For db design, it is not good to use enumerated data types and insert them to tables (maintenance is not comfortable, e.g. when you need to add some extensions…).

We suggest a standard design in PER model:
You will create a table (‘master table’ as you write). This master table will be a child table. Other tables will refer to it via standard relationship. To fill up this master table with default data, write an sql code to After Script section of this table.

Why not use LER model:
In LER model, you will not fill up a table with data via the After script.
Valid Values in LER work just for creation of check constraints in PER model during LER to PER conversion.

To sum up: At the moment TDM3 offers the alternative of creating a table and writing SQL code to After script. This code will fill up the table with appropriate data. At the moment, only this way is possible. However, in the future there will be an environment where you will be able to define the data in the table itself (without writing the sql code). - This is our plan.

If you have more questions, please write us. If you need to ask more specific questions, feel free to post them to modeling@quest.com. Thanks.

Regards,

Vladka + TDM Team

3

I did not want to have the enumerated values in LER in tables.
They can stay as they are.

Only the generator for Valid values LER=>PER would be extended - to offer alternate mode besides ENUM data type or check constraint. Similar to inheritance modes.

4

In TDM3, valid values = check constraints.

Let me inform you that we do not plan to extend the generator for valid values LER-PER.

If you do like, there is a possibility - you can modify the LER model and LER-PER conversion in metamodel on your own.

If you need more details, please write me back.

Regards,

Vladka