wiki:MemberstradsTable

Version 8 (modified by lemon-head, 11 years ago) (diff)

--

Memberstrads Table Restructuring Proposal

See http://www.bevolunteer.org/trac/wiki/The%20global%20model#memberstrads for the current table structure.

I think we should restructure the memberstrads and members tables.

Proposed SQL Table Structure

members

Here we should strip all the columns that link to translated text.

fields_on_profile

(or a similar table name)

This new table will have a short list of all the fields that are possible to have on a members profile.

columns:

  • id
  • name - a short name to be used inside the code.
  • wordcode (for the title, description, whatever). Evtl we don't even need this, but can use
  • description - some description, so the DB admin knows what it's about.

rows:

memberstrads

Here we store the language-specific texts.

remaining columns:

  • id
  • IdLanguage? - refers to a row in the languages table
  • IdOwner? - owner of the translated profile
  • IdField? - refers to one of the fields listed in fields_on_profile
  • IdTranslator? - who last updated the translation
  • updated - timestamp of last update
  • created - timestamp of creation
  • Sentence - translated text

The combination of IdLanguage?, IdOwner? and IdField? would be a unique identifier.

Why

It's more logical, and we don't need a new query for each of the fields.


Comment by JeanYves? :

I think I understand what you mean, but, all (almost) members fields which are supposed to have text are allread some kind of Id link to a memberstrads record, this allows to keep the size of members records small and this is good for the cache. memberstrads can also contain things which are not related to the members table (a groupmembership description for exemple or a special relation description, so the IdField? you are proposing will not be sufficient (this is why I have introduced the memberstrads.TableName? memberstrads.ColumnName? memberstrads.IdRecord? which are redundancy but allow for coherence control)). It is however true that for forums trads I choose not to use the memberstrads table but a forum_trads table (with a similar structure). I did this because I think someday the forum will be on another server. Anyway, the fact I choose to add new fields (TableName/TableColumn?) show that I made this memberstrads a bit to fast, they are certainly improvments to do + in future, this memberstrads will become teh table of all performance problem. IMHO, we should keep it as it is, but start to work on some memory resident image of this table, used with a good class and direct memory indexes to access it. This can be done in php or better in C++, or we can use some allready existing libraries, depend if we have someone ready to do some system programming ...


The problem with the current structure is that we cannot easily make one simple SQL for reading the entire profile page, because we cannot simply iterate the columns (not all columns point to translated text).

I did not know about the group membership descriptions and special relations. But I think for these we should make separate tables, to reflect the different structural roles.

-- lemon-head


Another solution will be to keep it all in the one memberstrads, with the following structure:

Alternative Proposal

memberstrads

Columns:

  • id
  • IdLanguage? - refers to a row in the table
    • languages
  • IdItem? - refers to an id in one of the following tables:
    • members
    • members_groups
    • specialrelations.
  • IdField? - refers to an id in the table
    • memberstrads_fields
  • IdTranslator?
  • updated
  • created
  • Sentence

memberstrads_fields

Columns:

  • id
  • name
  • associated_table - can be one of the following:
    • "members"
    • "members_groups"
    • "specialrelations"
  • wordcode (maybe we don't need it)
  • description (for DB admin)

Rows:

'''name''' '''associated_table''' '''description''' ======================== ====================== =========================== ProfileSummary members Occupation members MotivationForHospitality members FutureTrips members trips planned in the future Hobbies members Comment members_groups why member has joined the group Comment specialrelations comment about the relation ======================== ====================== ===========================

Then for members_groups, specialrelations, and members, we would not even need the columns with trad ids any more :)