Hi
I’m developing an entry-/results-system for running competitions. I have the system up and running for one competition but now I want to scale it up to handle several competitions.
All competitions have the same DB schema.
My first approach was to have one DB table for each competition and then another one to keep record of them all. In the last table, i planned to store the table names for the different tables.
My second approach was to only use one table and then add a field with an identifier for the competition.
Which way is best to go or is there another way of doing this?
If i choose the first approach, do I have to create one class/model for each table or can I use one and then point to different tables? I couldn’t find a xPDO-method for that.
Will the second way impact in performance if I have many records? Say that I will end up with 20 competitions with 2000 entries in each one of them (=40 000 records)? It will for sure impact on my ability to survey and manage the differnet competitions.
Best regards
Kristoffer
I am not sure, if I fully understand the problem, but IMHO the standard approach is to have (at least) two tables - the first table for competitions (data for each competition would occupy one record (line) in the table).
All the results data should be in another table (each result in a single record). Each record should contain an attribute competition_id (or so), which contains the ID of the related competition from the competition table.
Then you will be able to obtain an object for a particular competion (e.g. via newQuery or getObject methods) and the results for that competition via getMany method. Or, alternatively you can obtain the particular results directly using join or getCollectionGraph methods.
In general, the database schema should be relatively stable. It is rarely a good idea to have to add a table, if you need have a data record.
