RE: Modification in LiveUser SQL for Perm and Auth

> From: Bertrand Mansion [mailto:bmansion@mamasam.com] > Sent: Thursday, October 10, 2002 2:32 PM > 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. Well we just lowercased everything. I generally use uppercase for SQL "commands" and lowercase for tables. I don't know Oracle that much but this is the standard I have seen mostly. Can anyone confirm this issue with Oracle? > 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_...' shorter prefix is fine with me. I actually think we should also prefix the container type (auth and perm) as well 'lu_a_' or 'lu_p_'? > 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. Well that can get you very long field names ... But I don't have a preference ATM > 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. I would not want to do that. Stuff like that can change and then you have to change everything across the app. > 5. Use unsigned int() types where possible. This allow for more numbers > and > less disk usage as we don't need negative values. Sure why not > 6. Use varchar() instead of char() where char is not necessary. > Varchar() takes less disk usage. We are trying to keep the database MDB compatible. But I am currently talking to Manuel about moving to VARCHAR for MySQL (or any DB that support it). VARCHAR is slower under certain circumstances (in rare situations it even requires more disk space ..). But VARCHAR is still probably better because using CHAR forces us to use trim() etc ... > 7. Use index where they could speed up queries. Those are not complete ATM. There are some in there, but those need to be tweaked once we are down with the core and we know what queries are actually made Regards, Lukas