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 _______________________________________________ List: [email protected] Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/[email protected]/ Dev site: http://dev.catalyst.perl.org/
