to ensure that antville works with other databases than mysql in the backend we must change table- and column-names to avoid the use of reserved words (eg. "USER" is a reserved word in oracle and PostgreSQL). therefor i'd like to propose the following naming-convention:

• every table's name is prefixed with "AV_" (for "Antville").
• every column-name is prefixed with a three-character abbreviation of the table-name this column belongs to (plus some of the tables are renamed too): ACC_ in table AV_ACCESSLOG CHO_ in table AV_CHOICE FIL_ in table AV_FILE (the former GOODIE-table) IMG_ in table AV_IMAGE MBS_ in table AV_MEMBERSHIP (the former MEMBER-table) PLL_ in table AV_POLL SKN_ in table AV_SKIN SLG_ in table AV_SYSLOG TXT_ in table AV_TXT USR_ in table AV_USER VTE_ in table AV_VOTE SIT_ in table AV_SITE (the former WEBLOG-table)
• primary keys are named [table-abbreviation]_ID
• foreign keys are named [table-abbreviation]_F_[foreign table][_meaning], eg. IMG_F_USR_CREATOR, TXT_F_SIT, TXT_F_TXT_STORY, IMG_F_IMG_THUMB etc. (as an alternative, foreign keys could also be named as they're now (plus the abbreviation-prefix), but i personally think it's less consistent: think of TXT_CREATOR which actually references a record in the usertable). anyway, this is imo one of the points we should discuss

as a result the following prototypes should be renamed:

• weblog -> site
• goodie -> file (although this could easily be confused with File i agree with hns that it's easier to understand for users)
• member -> membership
• membership -> membermgr (the mountpoint "members" stays the same)

and for consistency-reasons the column AUTHOR in AV_TEXT should be renamed to TXT_F_USR_CREATOR (this is the only occurrence of AUTHOR in the whole db-structure).

please let me know what you think about it.

links: Oracle Reserved Words (v.9i) SQL Key Words (PostgreSQL) MySQL Reserved words

thanks robert for your suggestions. i can follow half the way, until the foreign keys rush in (ie +1 for table and column abbreviations so far).

concerning the foreign keys i don't have a better proposal but it makes me feel dizzy to read bafflegab like TXT_F_TXT_STORY (does this really mean that this is a foreign key pointing from the TEXT table to the TEXT table? well, probably for a comment, right? i also don't understand TXT_F_SIT... where's the third part here, the meaning?)

probably worth a discussion. or maybe i must get some more lessons in abstract thinking...

it's very important that we fix the db schema so it works on all databases. (Btw, I think it would be a good idea to try to add all the columns we know we'll need in the same swoosh, like for instance the per-story comment flag in the text table.) I must say that I don't like the per-table prefixes very much, it results in a very techie looking schema that's not very easy to memorize. If it's possible I'd prefer to just find names that aren't reserved in any system (like, for example, STORY instead of TEXT), but on the other hand, you don't have to deal with column names very often when programming in antville, so this is no vote against it, just a slight personal preference. Similar with the foreign keys naming conventions. I actually think it's clear from the type properties files what key relates to what column, so I don't really feel the need for such a formal convention (but I don't have a big problem with it either). +1 on the new names for the table names themselves (e.g. SITE instead of WEBLOG).