Re: Text search language field
От | Daniel Staal |
---|---|
Тема | Re: Text search language field |
Дата | |
Msg-id | CA9636FAE542DB8BBA4ACD34@mac-pro.magehandbook.com обсуждение исходный текст |
Ответ на | Text search language field (Daniel Staal <DStaal@usa.net>) |
Ответы |
Re: Text search language field
|
Список | pgsql-novice |
--As of May 13, 2012 2:15:36 PM +1200, Gavin Flower is alleged to have said: > I can't comment on how to implement the functionality you want, but I > have a few comments you may (or may not!) find useful. This of course was not the *complete* specs of the tables. I didn't feel the need to post pages of SQL for a simple question. ;) I just put in the relevant fields. > • Field names need not be in double quotes. I know. But I prefer to quote everything always, both to help them stand out in the text, and to make sure I don't have any case issues. > • if a character field is Always 3 characters, then say so It currently is always 3 characters, but I don't want to be to dogmatic about it, in case that needs to change in the future. (ISO codes have done that in the past, and I may decide a different/additional set of codes is needed at some point in the future.) Besides, there is no performance benefit in Postgres. (The opposite, actually...) > • add NOT NULL where appropriate (you may well decide more fields > aught to be NOT NULL) Generally I have, though not always I'll admit. 'Primary key' implies it, and that was the only one that actually has that restriction in the selection of fields I showed. > • keeping primary and foreign key fields separate from user visible > data. I disagree strongly here. ;) Primary keys should be whatever is suitable for the data; creating an artificial primary key (when not needed) is just storing more data and doing more work. It also gives a feeling of 'safety' which doesn't actually exist, as you can then create data that is invalid, but that fits because your table design allows it. In particular, the language table keeping the the 'code' unique and distinct is the *entire point* of the table, so there is no good reason to use anything else as the primary key. (I do have a generated ID in the resource table, though it's a much more complex generation than a simple serial. Again, I didn't feel the need to show it.) This does mean thinking through your future use-cases a bit more at the initial design stage, but if I wasn't willing to do that I wouldn't be asking this question in the first place. (Actually, the languages table is nearly exclusively used inside the database, so you could claim it *is* separate from user visible data. I could almost use an enum there instead, but having a linked description available might be useful on occasion. I also thought that knowing it exists might be useful in solving my problem: One possible issue is that text is not the correct data type, and having a mapping table available might be useful.) > • identifying primary and foreign key fields clearly Agreed, although I only showed one of each. ;) > • suggest table names be singular (my convention, not universally > adopted!) I tend to use plural or singular depending on how they will be used: 'resource' will tend to be used one at a time, while 'languages' will tend to be used as a reference list, and therefore as an aggregate. A more defined naming scheme might be useful I'll admit. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
В списке pgsql-novice по дате отправления: