I want to make application for studying boardgame (chess). Idea is to make collection of positions where I made poor move. I want to be able to add from many possible themes to a position and search collection by different themes to practice those types of positions. I cant make up my mind wether to simply make relational database schema and use MySQL or if I should design an XML Schema. I think for XML choice: Pros: Could be reused for displaying positions on my website and easier "communication" with apps i write later. Cons: Maybe speed when collection grows large ? SQL: Cons: Sometimes many themes for position sometimes only one and no array type in MySQL so would need to set a max number of themes and have many empty rows for many positions. Pro: Speed.

I will be adding new positions regularly and maybe new themes will show up. What i was thinking about con for MySQL was this:
Say I make table with primary key FEN code. FEN code is string wich uniquely identifies a chess position. Now i have to make 1 column for each theme so when creating database will have to fix number of themes eg the following could be the table:

FEN code | Error size | Theme 1 | Theme 2 | ..... | Theme n |

Addded: Forgot to ask something about XML: What would be "best" you: The FEN code seems like natural primary key in relational database but in the XML would you prefer just this cryptic FEN string or a more verbose human friendly description of the chess position ? One could simple somehow create XML tags for each board element and then have XML elements for the different chess pieces.

This would be fairly poor design fo the reason you've already spotted: its not easily extensible. When you have n attributes of a simmilar class of thing that is usually best represented by a distinct entity, e.g.: An entity with Fen Code and Error Size, which is related to another entity that describes themes.

Also, natural primary keys are best avoided. Surrogate keys fit the requirements of a primary key better (they are unique, unchanging and never null). Natural keys tend to suffer from the unchanging bit.

Is this a single user application? If it is a releational database could be overkill and you might just consider an plain old text file.

Paul Sturrock wrote:This would be fairly poor design fo the reason you've already spotted: its not easily extensible. When you have n attributes of a simmilar class of thing that is usually best represented by a distinct entity, e.g.: An entity with Fen Code and Error Size, which is related to another entity that describes themes.

Also, natural primary keys are best avoided. Surrogate keys fit the requirements of a primary key better (they are unique, unchanging and never null). Natural keys tend to suffer from the unchanging bit.

Is this a single user application? If it is a releational database could be overkill and you might just consider an plain old text file.

Thank you for reply. About overkill: This is partly for being useful to me, partly for doing some Java coding, so overkill is ok If someone made a good design of such an app it could be natural to share a database with more people.

Once a position is in the database it will never change. What does "natural primary key" mean versus "surrogate key" ?

A natural key is an attribute of an entity that has a logical association with the other attributes of that entity. For example, email address might be a natural key for a "user" entity (where the other attributes might be name, date of birth etc.). Natural keys make more sense from abstract ER modelling and have the advantage of being understandable within the context of whatever data is being modelled. However, even when we are pretty sure at modelling time that our natural key will never change real experiences suggest there is always a chance, which is why a surrogate key is preferable - it has no business meaning so will not change.