darren at darrenduncan
May 19, 2009, 2:34 PM
Post #13 of 14
(1386 views)
Permalink
|
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)
)
For example, with 1 table:
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,
...,
multilang_attrs TABLE (
lang Lang PRIMARY KEY,
name Text NOT NULL,
description Text,
duration Text,
fee Text,
...,
)
)
Hopefully those should be self-explanatory.
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),
...
)
Or alternately:
CREATE TABLE app_form_fields (
field_name Text PRIMARY KEY,
field_label TABLE (
lang Lang PRIMARY KEY,
text Text NOT NULL
),
input_constr_pattern Text,
constr_fail_msg TABLE (
lang Lang PRIMARY KEY,
text Text NOT NULL
),
...
)
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.
I hope that answers your question. Mainly the first/general answer I think is
more applicable in your case?
-- Darren Duncan
P.S. My use of data types like StudyId and Lang etc aren't mistakes or just
illustrations. Besides the fact that the original examples did similar, users
should be able to define their own data types. For example, StudyId may be just
defined as consisting of an Integer, but it is still a disjoint type and using
Integer ops on it like addition or division should be caught by the compiler as
an error as they don't make sense (what is the meaning of adding two study ids?).
_______________________________________________
List: Catalyst [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/
|
