wiki:MemberstradsTable
Last modified 3 years ago

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.

In Short

Version I

members:
  fields: // the usual ones, except the keys that link to translated text
  rules:

memberstrads:
  fields: [id, IdLanguage, IdOwner, IdField, IdTranslator, updated, created, Sentence]
  rules:
    language:                IdLanguage = languages.id
    profile owner:           IdOwner = members.id
    field on profile:        IdField = fields_on_profile.id
    recently translated by:  IdTranslator = members.id

fields_on_profile:
  fields: [id, name, (wordcode), description]
  rules:

Version II

members:
  fields: // as before, but without the links to memberstrads

groups:
  fields:  // as before, but without the links to memberstrads

memberstrads_fields:
  fields: [id, name, associated_table, wordcode, description]
  refer in php code:
    - name

memberstrads:
  fields: [id, IdLangauge, IdItem, IdField, IdTranslator, updated, created, Sentence]
  rules:
    language:   IdLanguage = languages.id
    field:      IdField = memberstrads_fields.id
    translator: IdTranslator = members.id
    profile:    IdItem = members.id  &&  field.associated_table = 'members'
    group:      IdItem = groups.id   &&  field.associated_table = 'groups'
    friendship: IdItem = friends.id  &&  field.associated_table = 'friends'

Version III

members:
  fields: // as before, but without the links to memberstrads

groups:
  fields:  // as before, but without the links to memberstrads

memberstrads:
  fields: [id, fieldName, tableName, IdLangauge, IdItem, IdTranslator, updated, created, Sentence]
  rules:
    language:   IdLanguage = languages.id
    translator: IdTranslator = members.id
    profile:    IdItem = members.id  &&  tableName = 'members'
    group:      IdItem = groups.id   &&  tableName = 'groups'
    friendship: IdItem = friends.id  &&  tableName = 'friends'

Proposed SQL Table Structure, Version I

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.TableColumnName 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 :)

JY update August 2009:

This discussion has not find a conclusion. The current structure of memberstrads is

id : primary key
IdLanguage : foreign key to languages table
IdOwner : the owner of the translation data
IdTrad : the key (to be used with IdLanguage?) which allows to point on a specific members trads. For exemple members.ProfileSummary points to an IdTrad
IdTranslator: the if of the last guy who update the data (usually the member himself (ie : IdOwner) but it can be someone else) updated: when the data was updated
created : when the data was created
Type: reflect if it is the member himself, and admin or a translator (another member who has right to translate someone else profile for example) who has updated the profile.. This is informative
!Sentence : the text data
IdRecord : the id of the record (for exemple a members.id) which point to this translation
TableColumn: the name of the table and the column the translation is associated with.

Nota: IdRecord and TableColumn are redudancies. They are used for maintenance (and keep in mind that updates of memberstrads are not embedded within transations). They are corresponding to the "admin description" lemon-head proposed to have.

Currently, fields from teh following tables can be memberstrads foreign keys: members,specialrelations, groupsmembership

To use membertrads, only use MOD_WORDS.
It provides:

  • MOD_WORDS::InsertInMTrad to create a value (be careful to deal well with the IdRecord? and TableColumn?, at worst it can be rebuilt after the insert)
  • MOD_WORDS::ReplaceInMTrad to update a value
  • MOD_WORDS::mTrad retrieve the value according to the language the user is surfing in (or english or first available language if not found)
  • MOD_WORDS::mInTradretrieve the value according to a specific language (or english or first available language if not found)

In future this will need to use memcache because it will become a performance issue.

the translations table is similar to memberstrads and has similar functions in MOD_words. The difference is that translations table is used for things which are not connected to members profiles or members data (like forums translations, polls translations, etc ...).
forums_trads is just an alias of translations (its a view of the table) This is an important difference since these data can remain if the member who created them leaves BW.