Adding object details table to propel

1
votes

I'm trying to move over from my own pseudo-ORM to using Propel (as my previous code was effectively Active Record) and and struggling to find the 'best' way to solve the problem of having classes which are composed of both changing and fixed properties.

It's for a game where I have regions on the map which can change owner throughout the game, so I need to store the game id (as multiple games can be going on at once) and the current owner on the regions table, but I also need to reference things like the name of the region, whether you can recruit there, the starting owner, etc.; all of these are fixed for that region and don't change within the game or between games.

For instance, the region 'Kent' might be initially owned by the English, but could be taken by the French, but it's always called 'Kent', you can always recruit there and it's always part of the larger country 'England'.

Options I can see are:

  1. Having a lookup .ini file or database table not controlled by Propel and just having the name etc. as fields on the main regions table
  2. Have a 'details' table and a 'details_id' as a field on the regions table
  3. Create a class for every region with the 'details' hard coded (though this seems difficult to maintain and error check to me)
  4. Something 'better' that I haven't though of

This must be a standard problem and I apologise for not getting the search terms right, but I've been struggling with it on and off for a while now and can't make something work intuitively!

As requested to clarify my database structure -

Region table

id  | game_id | owner | turn_last_conquered | details_id | population
----|---------|-------|---------------------|------------|-----------
1   |  100    |England|    2                |    1       |  10
2   |  101    | France|    3                |    1       |  12

Region Details table

id  | name | port | fortified | starting owner | starting population
----|------|------|-----------|----------------|--------------------
 1  | Kent |  1   |   0       |  England       |  13

Both entries in the region table correspond to Kent in different games. If I had the properties as part of the Region object/table then if I wanted to change the name of something in every game, I'd have to update all the affected rows, whereas if it was in a separate table then I'd only have to update that one and the change would propagate. My own system populated the Region object with the required fields, with Propel I'm trying to find a way around having a 'RegionDetails' object, but maybe I shouldn't be.

1
phpmysqlpropel
Only a problem is standard, it must not mean that there is a standard solution. Keep all the data in the same storage layer is the first suggestion I can give on a quick first read, but be aware that your question might lead to discussion and will be closed then as it is could be opinionated. – hakre
And another hint: First move to propel. Then change the database design according to the needs. Don't intermix both operations. – hakre
I do this all the time. I don't understand the difficult you are having. I would just put all the data associated with a region (name, owner, etc) in the region table. I don't understand why they would be a problem. Can you help me see what problems you are seeing with that approach? – Ben
@Ben It's possible I'm getting myself in a tizzy about normalisation, seems a 'better' approach to have the other details in a lookup table. If I change the name of a region then with your approach I'd have to update every entry in the region table that was affected, rather than just the lookup table. Of course, that update would be very easy... – Uhtoff
I really don't see how storing the owner id and the region name in the same table is denormalized. I must not be seeing the same database structure that you are seeing. I think the crux of your question hinges on the difference between what you see as "fixed" and "changing" properties. I really still don't understand your question. Maybe it would be helpful to see how it is currently done so I can show how the same database structure might be used with Propel. – Ben

1 Answers

2
votes

If you are going to use multiple tables, and you are going to use Propel (or some other Active Record library) you are going to have to have a class per table. This is pretty much the definition of Active Record. One object per row in the database and one class per table.