Re: Modification in LiveUser SQL for Perm and Auth

Am Thu, 10 Oct 2002 14:33:59 +0200 schrieb Pierre-Alain Joye <paj@pearfr.org>: > 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. +1 > > 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. +1 > > 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. Here I don´t completely agree... I can´t tell you a proper reason for not doing it this way, I just don´t like it and it´s not the way things are done at our company, where we mainly use MySQL and MSSQL Server and simply aren´t used to doing things this way. Another thing: Actually it may vary from container to container (assuming that we´ll have some more perm containers soon, each one bringing its own permission scheme) which tables/fields are there and how they are named. So all we´re currently talking about is the one DB_complex container we have now. > > 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 See my above statement. > > 5. Use unsigned int() types where possible. This allow for more > > numbers and > > less disk usage as we don't need negative values. > > :-)) +1 > > 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 ? I´m a varchar fan too, but Lukas says this isn´t MDB-compatible and it would be nice to use the same database scheme with DB and MDB interchangeably. > > 7. Use index where they could speed up queries. > > indeed. +1, but someone else will have to do this as I´m not an RDBMS performance optimizing guru... also, I think it might differ between databases how they´re handling indexes and query optimization... Regards, Markus -- *21st Media* | Consulting, Konzeption, Produktion für die Bereiche: Markus Wolff | Internet, Intranet, eCommerce, Content Management, Hamburg,Germany | Softwareentwicklung, 3D-Animation, Videostreaming http://21st.de | Tel. [+49](0)40/6887949-0, Fax: [+49](0)40/6887949-1