Lemongrass Posted July 29, 2012 Group: Developer Topic Count: 28 Topics Per Day: 0.01 Content Count: 547 Reputation: 270 Joined: 11/08/11 Last Seen: December 26, 2023 Share Posted July 29, 2012 Well since I was tortured at school with database designing I thought I will give it a try and post some of my ideas in here. What I am talking about is the whole structure of the current (SQL) database. A lot of data is saved redundant and the integrity of the data is not given on database side. Why is this important? So let us say some user edits some of their entries in the character table. Something went wrong with the saving of a wedding and it is only partially stored into the database. The administrator puts the id of the husband/wife manually into the database and does a typo with the ID of the character. He will not be able to recognize his mistake at that point of time, but he will have to look for it later on. And that is a problem that could easily be fixed with the usage of foreign keys. But there would be a lot more of awesome features like for example check constraints which are not featured by MySQL but there still is a workaround for it. This could be used to check buy/sell price values. That is something that came to my mind when I was told about a bug that existed on euRO about 2 weeks ago, where you could buy some item for 1000z which would give you 1000z on usage, but that one could be sold for ~1300z to the npc because the database definition was wrong. So my question here is: Would the developers want someone who redesigns the database or do they even care about the ideas I mentioned above? If you want me to do it or to give you a small preview or something, I would not mind. I just do not want to design the whole database and it is not used afterwards. So let me know, if you do not want to discuss this matter publicaly, let me know how we can talk these things out. 3 Link to comment Share on other sites More sharing options...
saithis Posted July 29, 2012 Group: Members Topic Count: 2 Topics Per Day: 0.00 Content Count: 40 Reputation: 20 Joined: 12/31/11 Last Seen: May 10, 2013 Share Posted July 29, 2012 I think the reason why some data is saved redundant (for example some guild table fields) is, because of the possibility to run the login, char and map server stuff in different databases. So you couldn't get the data with joins and we could use foreign keys only for tables of the same server. And currently MyISAM is used as storage engine, we would have to switch to InnoDB to use foreign keys (MyISAM doesn't support them). 1 Link to comment Share on other sites More sharing options...
Lemongrass Posted July 29, 2012 Group: Developer Topic Count: 28 Topics Per Day: 0.01 Content Count: 547 Reputation: 270 Joined: 11/08/11 Last Seen: December 26, 2023 Author Share Posted July 29, 2012 That's a good point, but I do not really get the point of splitting these tables up into different databases. But even though if that would be the reason, we should think of dropping support for that and getting the data consistent and free of redundancy. As for the engine it would be required to switch to InnoDB that is indeed correct. The things I have mentioned so far, are far away of what I personally would change all in all. Since there would be tons of more things I would change regarding the database. But some of them will follow, if some devs show some interest in this topic. Link to comment Share on other sites More sharing options...
Brian Posted July 30, 2012 Group: Members Topic Count: 75 Topics Per Day: 0.02 Content Count: 2223 Reputation: 593 Joined: 10/26/11 Last Seen: June 2, 2018 Share Posted July 30, 2012 Having foreign key constraints would be handy when doing this For your other check constraints examples (checking if partner_id exists in char.char_id and checking buy/sell price values), does MySQL support that? I know MSSQL does, but I don't know how to in MySQL. Link to comment Share on other sites More sharing options...
Lemongrass Posted July 30, 2012 Group: Developer Topic Count: 28 Topics Per Day: 0.01 Content Count: 547 Reputation: 270 Joined: 11/08/11 Last Seen: December 26, 2023 Author Share Posted July 30, 2012 The thing with the partner id is non other than another foreign key. The price checking has to be done with a little work around since MySQL does not have a check constraint. It has to be implemented with a stored procedure that is called by a trigger. But yes it is possible. About the merge of two databases that would be a very easy task, because we could even add cascade options to the foreign keys. The only problem that came in to my mind immediatly is the merging of the character and login names, since there could be duplicate name entries for the two servers. Link to comment Share on other sites More sharing options...
saithis Posted July 30, 2012 Group: Members Topic Count: 2 Topics Per Day: 0.00 Content Count: 40 Reputation: 20 Joined: 12/31/11 Last Seen: May 10, 2013 Share Posted July 30, 2012 That's a good point, but I do not really get the point of splitting these tables up into different databases. If you use 1 login server for 2 char servers and 2 map servers, then it makes the whole thing easier, when you use 3 databases (1x login and 2x char/map). If you don't split them, then you would have to rename half of the tables. But I also don't know where you would want to split char and map databases... Link to comment Share on other sites More sharing options...
Kenpachi Posted July 30, 2012 Group: Members Topic Count: 22 Topics Per Day: 0.00 Content Count: 764 Reputation: 220 Joined: 11/14/11 Last Seen: November 19, 2020 Share Posted July 30, 2012 Dude, school knowledge is one thing, experience another... Redundancy isn't always a bad thing. It can boost the performance and useablity. I'm working with SQL databases everyday and know many companies which do so. Neither me nor one of those campanies uses the foreign key enigine which is shipped with the SQL server. Why this? Pretty simple... Your application should manage the data and the processes, not the SQL server. The SQL server is just a stupid data holding backend which should be replaceable in no time. When you pack it with tons of business logic that's nearly impossible... 4 Link to comment Share on other sites More sharing options...
Lemongrass Posted July 30, 2012 Group: Developer Topic Count: 28 Topics Per Day: 0.01 Content Count: 547 Reputation: 270 Joined: 11/08/11 Last Seen: December 26, 2023 Author Share Posted July 30, 2012 Your application should manage the data and the processes, not the SQL server. From a guy that puts in a UNIQUE constraint for a 3rd party tool, that should handle the business logic. Do not get me wrong here, I do know that redundancy can be used to boost performance, but I do not think that the logs for example do need that much of performance. I also use redundancy when I optimize performance on some databases and I also work with huge companies and their databases, not everyday though. I do appreciate your opinion to this topic, but even if we do not use foreign keys or something like that, could you please set up some naming standards for the database and remove unneeded redundant columns? Link to comment Share on other sites More sharing options...
Kenpachi Posted July 30, 2012 Group: Members Topic Count: 22 Topics Per Day: 0.00 Content Count: 764 Reputation: 220 Joined: 11/14/11 Last Seen: November 19, 2020 Share Posted July 30, 2012 Your application should manage the data and the processes, not the SQL server. From a guy that puts in a UNIQUE constraint for a 3rd party tool, that should handle the business logic. Well, that's something our application can't control so there was no other choice. Do not get me wrong here, I do know that redundancy can be used to boost performance, but I do not think that the logs for example do need that much of performance. I also use redundancy when I optimize performance on some databases and I also work with huge companies and their databases, not everyday though. I do appreciate your opinion to this topic, but even if we do not use foreign keys or something like that, could you please set up some naming standards for the database and remove unneeded redundant columns? Sure, I'll work on it in next days. Link to comment Share on other sites More sharing options...
Lemongrass Posted July 30, 2012 Group: Developer Topic Count: 28 Topics Per Day: 0.01 Content Count: 547 Reputation: 270 Joined: 11/08/11 Last Seen: December 26, 2023 Author Share Posted July 30, 2012 So if I release a shitty tool that spoils the data foreign keys will be added? Link to comment Share on other sites More sharing options...
Recommended Posts