[Catalyst] More natural access to model?

Paweł Tęcza ptecza at uw.edu.pl
Fri Jun 5 09:26:32 GMT 2009


Darren Duncan pisze:
> Paweł Tęcza wrote:
>> Yes, I'm trying to think about my data as about the objects with
>> attributes and create simple and flexible database without data
>> redundancy. For example, I have tables for users and their roles,
>> studies, units, registrations. I also have tables for multilanguage
>> attributes of studies and units. Finally, I have tables for
>> relationships beetwen users and their roles, units and their attributes,
>> studies and their attributes, etc.
>> 
>> I'm very curious what database schema is the best for me in your
>> opinion. Of course, I don't ask you for desing of the whole database.
>> But could you please show me what tables I should create to store
>> information about studies? :)
>> 
>> Please remember that a study has a lot multilingual attributes (name,
>> description, duration, fee, etc) and non-multilingual attributes
>> (student limits, dean's office, its address, phone number, fax and web
>> site, study manager, his name, e-mail, and phone number, etc).
> 
> Though I haven't actually gotten around to implementing a multilingual app yet, 
> I have thought these issues since long ago, so I'll summarize some principles here.
> 
> In the general case where the solution details are specific to each problem, the 
> answer is to simply take the proper design of a unilingual schema, where each 
> piece of information has its own table column, and add a new language-specifier 
> column, and include this column in the table's key(s) so what used to be a 
> single record is now allowed to be a multiplicity varying on the language.
> 
> And normalize appropriately to avoid redundant data.  Either by splitting each 
> table with multilingual elements into 2 tables, with multilingual separated from 
> unilingual (the multi gets the lang-spec column).  Or by turning each 
> multilingual element or inter-dependent set of such into a collection-typed 
> attribute of the single table.  Some SQL DBMSs support the latter, and all 
> support the former; I'll demonstrate both.
> 
> For example, with 2 tables:
> 
>    CREATE TABLE studies (
>      study_id StudyID PRIMARY KEY,
>      unit_id UnitID,
>      status Status,
>      limit_soft Integer,
>      limit_hard Integer,
>      deans_office Text,
>      address Text,
>      phone Text,
>      fax Text,
>      web_addr Text,
>      ...
>    )
> 
>    CREATE TABLE studies_multilang_attrs (
>      study_id StudyID FOREIGN KEY REFERENCES studies (study_id),
>      lang Lang NOT NULL,
>      name Text,
>      description Text,
>      duration Text,
>      fee Text,
>      ...,
>      PRIMARY KEY (study_id, lang)
>    )

Hello Darren,

I'm so sorry for a long silence, but I had a lot of work in the last
weeks...

Thank you veru much for your interesting message! I designed my data
base schema before in very similar way like you :)

> Now in a common special case of multi-lingual apps, where your user interface is 
> data defined so for example the text strings you display to users such as 
> greetings or prompt messages or form field names etc are stored in data, you 
> could either take the same approach as above, or alternately you could invert 
> the design and just have a single large strings table and then all other tables 
> have foreign keys into it using message ids that each is in common for all 
> language variants of the message.
> 
> For example:
> 
>    CREATE TABLE app_user_texts (
>      text_id Text NOT NULL,
>      lang Lang NOT NULL,
>      text Text NOT NULL,
>      PRIMARY KEY (text_id, lang)
>    )
> 
>    CREATE TABLE app_form_fields (
>      field_name Text PRIMARY KEY,
>      field_label Text FOREIGN KEY REFERENCES app_user_texts (text_id),
>      input_constr_pattern Text,
>      constr_fail_msg Text FOREIGN KEY REFERENCES app_user_texts (text_id),
>      ...
>    )

Hm. It's new and interesting idea for me. I always store user interface
data in the template files. Together with multi-lingual logic.

I guess I can also put error/warning messages into my data base. But it
has one weak point. If connection to database is broken, then user can't
see any messages...

> Also self-explanatory I hope.  Regarding this design method, see also how Mac OS 
> X works, how it does multi-lingual strings support in apps, but that I think it 
> uses XML files instead of a SQL db but the principle is the same.  Similarly, 
> this latter sort of design could just use app resource files in general to hold 
> the strings, organized that way, rather than a SQL db.  Where a SQL db is useful 
> is if your app is of the CMS variety where users are defining what app elements 
> exist at runtime, and usually this info is stored in a database.

Trying Mac OS X will not be easy for me, because I don't have any
machine with it. I'm Linux and OpenSolaris user :)

Storing interface data in the files is probably more safe solution...
But I agree that putting them into data base has advantages too :)

My best regards,

Pawel



More information about the Catalyst mailing list