From #openttdcoop wiki
If accountability is a concern, there are a few more things you'll want to add to your database schema. In particular, a last_modified time (and optionally a last_editor) column on some of the tables to indicate when (and by whom) the last modifications were done. At the very least, the configuration/server tables should probably have this information. You may also want to add them to the configuration_* tables to indicate the changes that were made.
Also, will you need a security table to store authorized users (and passwords) which will grant people access to the modification abilities? You may also consider a simple audit_trail table, which contains a list of users, times, and actions performed using the web-based tool. You may have already designed this table, and have not displayed it for security reasons. Other alternatives would require linking this tool in with an existing security module, like a web or wiki login.
Again, if accountability is not a concern (now or ever), you can disregard this comment. -- Thraxian 13:51, 7 October 2008 (UTC)
- Thanks for your comments. I added the authorization and logging part. Didnt have time for that. Please comment on the design v0,02 -- Kommer 18:14, 8 October 2008 (UTC) (previously unsigned)
What is the purpose of the value table? It appears that each of the tables in the server_* and configuration_* tables contain a value for each option. Does this table hold a list of possible values for an option (like in the case of breakdowns, none, reduced, normal, etc.?) If so, the value table might want to hold both a name and a value. The name would be displayed in the drop-down list of options, while the value would actually be used in the server_* and configuration_* tables. -- Thraxian 12:52, 9 October 2008 (UTC)
- Your assumption is right. Added a name to the table Kommer 13:27, 10 October 2008 (UTC)
Do you want to add the ability to automatically detect GRF requirements and add them as appropriate? It might make the tool a bit more complex than intended. A new GRF_requirements table would contain the ID of one GRF and the ID of one of its pre-requisites. If a user adds a GRF, this requirements table would be checked and any required GRFs not currently added will automatically be added, and ordered directly before the user-added GRF. The problem arises when a user re-orders the GRF list (if that is possible), to ensure that the requirements are still automatically placed earlier in the order than the GRF that needs them. Could make things complex, but you might consider that as a future expansion of the database design. The new table would be newgrf_requirement (requirement_id PK, newgrf_id FK1 [newgrf.newgrf_ID], required_newgrf_id FK2 [newgrf.newgrf_ID]). -- Thraxian 12:52, 9 October 2008 (UTC)
- Good idea. Dont think I will implement it in version 1 but the table is already added Kommer 13:27, 10 October 2008 (UTC)
Do we want to keep track of GRF updates and when they did become absolete in the database? Or do we physically remove the deleted GRF from the database? Perhaps we should think about something like GRF_add_date_ID, GRF_prev_date_ID, GRF_lastupdate_date_ID. The first ID never changes, but in the database you can keep a kind of history of a GRF. Perhaps it is just a step too far for something like this? (Bit related to the accountability part of Thraxian.) --Tneo 10:10, 12 October 2008 (UTC)
User Group relationship
The current schema allows a user to be associated with one and only one server group. Is this relationship by design? Would it be better to allow users to belong to more than one server group (for instance, read/write on PS but read-only on Dev). The current solution for this scenario would be a group for users that have these specific rights. By assigning the users to groups through another table, you could have a read/write PS group and a read-only Dev group, and the user is a member of both. The new table would be user_group_member(member_ID PK, user_id FK1 [user.user_ID], group_id FK2 [group.group_ID]), and group_ID would no longer exist in the user table. Thoughts? -- Thraxian 12:52, 9 October 2008 (UTC)
- Already changed that :) Kommer 13:28, 10 October 2008 (UTC)
What is the scope of this project? Is the purpose to provide a CFG file only? What about some of these other features?
- Update server to new version (nightly/stable)
- Stop/Start/Pause/Unpause the server
- Save & Archive the CFG and SAV of the active game
- Map configuration for new game
Many of these features may already be handled by autopilot, but to my knowledge, there isn't a web-based tool to run that, and I don't know of any IRC reference on how someone with appropriate rights can run autopilot commands. A web-based auto-pilot (or at least some documentation - internal or wiki) may be a separate project, but it would be good to clarify what this web-based configuration tool is NOT going to do. -- Thraxian 12:52, 9 October 2008 (UTC)
- The project scope is now updated on the main page -- Kommer 13:16, 10 October 2008 (UTC)
Just a thought and I couldn't find anything about that explicitly yet. Some GRF's require to be loaded before others and have certain dependencies. Will the tool take care of the dependencies, like make those that don't work with chosen climate be unavailable? Will the tool order the GRF list so that they are loaded in the proper order? Do we want to be able to load various trainsets in a game, or seen possible conflicts in GRF's do we always use one trainset? Is it needed in the database to mark trainset GRF's as such that you can only pick one? Or can PHP handle that, wherefor I presume you still need to know wich GRF is a trainset and which is not. --Tneo 10:06, 12 October 2008 (UTC)