I have a question regarding the way silverstripe persists related objects to the database.
Say I have these entities: Members and Events. Each member can sign up for multiple Events.
My Events table (in DB) looks fine. When ever I add an Event to a members list of Events (using has_many (Events => Event)) I see that a row pops up in the Events_Versions with the same info as the original row contains in the Events table.
I haven't tried to update the Event row but I assume that all the versioned rows also will be updated - Here I fear a performance problem. And then there is the general redundancy issue with having the same data over and over again. I realise that it's difficult to created ORM with nice DB structure - But does this issue concern any other than me?
Fair enough if its a fairly small site, but what if it explodes - How would performance then be?
Every time you call write() on an object it'll save it to the versioned table (if of course they have versioning enabled). AFAIK most of the possible performance overhead is keep at bay because the latest object is in the Events table and so very few queries (reads, sorts) happen on the versioned table - As I see it you would only write to it and viewing it only occurs in the backend.
If your editing your object millions of times that table will get quite big but for most operations you won't deal with queries on the versioned tables. Of course duplication of data should be avoided and versioning should only create new versions when data has changed - I thought it already did this but I could be wrong.
If Events was extending DataObject instead of Page would the versioning then be deactivated? Or in other words, how can I disable versioning on specific classes? And if versioning is disabled how would the relations be created?
Perhaps I misunderstod how the relations work. I thought that a relation between a Member and an Event was created in the versioned (duplicated) row in the _Versions table. Is this wrong?
By default dataobjects do not have versioning enabled. If you don't have versioning this the data is just changed on the object in the events table. For a has many relationship a column 'RelationshipID' is created on the opposite class site so it simply runs a UPDATE. With the many_many tables - which store many_many relationships yes this would get big, we have a couple with 2 millon plus but thats indexed so lookups are relevantly quick.