Re: Modification in LiveUser SQL for Perm and Auth

On Thu, 10 Oct 2002 14:32:28 +0200 Bertrand Mansion <bmansion@mamasam.com> wrote: > Hi, > > I would like to make some modifications in the SQL for the liveuser > tables but they might not be backward compatibles so take this message > as a suggestion only and tell me what you think. > > 1. I would prefer to use uppercase for field and table names: > This is because I already had trouble with Oracle converting all > names to uppercase by default, so I took the habit to uppercase > everything. I prefer lower case everywhere. > 2. Use a prefix for liveuser tables: > This is useful to know that the tables relate to a certain package. > It's already the case, we use 'liveuser_...' but I would suggest > something shorter like 'LU_...' Here I really do not care, I m working on customisable table/field names. After it ll be done, that does not have any importance. > 3. Use a table prefix for table fields: > I found this particularly useful not to mix field names between > tables in SQL queries. I know it's possible to use (1) TABLE.FIELD > or (2) A.FIELD when an alias is defined for TABLE A, but this still > make long queries and, like in German, you have to read the whole > sentence to get the meaning. My solution is to use for example > ARNA_DEFINE_NAME for the area define name of the table AREA_NAME. Same as pt 3. but, I do not care about long SQL sentences, keeping them easy to match is better than short words we ll all forget after a few weeks. > 4. Use PK and FK to differenciate Primary Keys and Foreign Keys. > For instance, ARNA_PKAREA_NAME for AREA_NAME table primary key and > anywhere else RIGHT_FKAREA_NAME to know this field is a foreign key > related to a PKAREA_NAME in another table. +1, but this depends on pt.2 and pt.3 > 5. Use unsigned int() types where possible. This allow for more > numbers and > less disk usage as we don't need negative values. :-)) > 6. Use varchar() instead of char() where char is not necessary. > Varchar() takes less disk usage. Disk usage is not a point for me :-), is varchar suitable for *all* rdbms we used in DB ? > 7. Use index where they could speed up queries. indeed. > Note that I am not a DB expert, these are just habits I took which I > found really useful on the longer run. Still, I am quite open to other > suggestions. I usually like optimisations as far they do not break portability. :-) hth pa