Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Jocho
Godless Horizon. WHY so Seri0Us
0
|
Posted - 2012.11.16 04:44:00 -
[1] - Quote
Does anyone know of a good way of storing eve fittings in mysql that doesn't break the normalisation rules?
I'm making a site that stores alliance fittings and have one table that stores ship details (name, type, tech level etc) and another table that stores module information (name, price etc). What is a good way of then creating a fitting table without having to have a table for each class of ship or many null values? |
TheSkeptic
Federal Navy Academy Gallente Federation
61
|
Posted - 2012.11.16 08:44:00 -
[2] - Quote
Store the fittings as ship DNA
Then when displaying the information just parse the fitting DNA.
http://wiki.eveonline.com/en/wiki/Ship_DNA Lord Zim: I do the following in hisec, because it's less effort vs reward than doing the same in nullsec: - Ratting
|
Steve Ronuken
Fuzzwork Enterprises
788
|
Posted - 2012.11.16 13:20:00 -
[3] - Quote
If you do end up using ship DNA, take a look at https://github.com/fuzzysteve/Ship.js Might be of interest.
If you're /really/ interested in a normalised form of storage (and want to be able to search for all ships with a particular module, which is the only reason to break it out into a database understandable form) something like:
Fitting table --- id, Ship type, name, other meta data you might want to stick on it.
Module table --- id, fitting id, module id, module quantity, possibly an ammo id if you want to be able to associate ammo at that level, possibly a module type if you want an easy 'high/medium/low/subsystem/ammo/drone' differentiation.
So you'd have one entry in the fitting table, and multiple entries in the module table.
The module type would be because it's a bit of a pain having to join on dgmTypeAttributes every time you want to pull a list of what module goes in what slot. It breaks with full normalisation, but it's not a biggy, as the module slots don't change FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Jocho
Godless Horizon. WHY so Seri0Us
0
|
Posted - 2012.11.16 20:48:00 -
[4] - Quote
Thanks guys, really useful info. |
Indalecia
69
|
Posted - 2012.11.16 22:50:00 -
[5] - Quote
TheSkeptic wrote:Store the fittings as ship DNA Then when displaying the information just parse the fitting DNA. http://wiki.eveonline.com/en/wiki/Ship_DNAEdit: another advantage is you can then create pages for the IGB with the fitting links that when clicked should open the fitting in game
This page is completely wrong anyway, the format does not work like this at all. I don't reccomend storing DNA in the database either, because the format is incomplete, ambiguous and sparsely defined at best.
There is no silver bullet to this problem. Either you have to go with lots of tables and foreign key constraints to do it "properly", or you could store something else in the table (storing the CLF form comes to mind, although you lose the ability to write smart queries to pull specific data out of it). |
Steve Ronuken
Fuzzwork Enterprises
788
|
Posted - 2012.11.16 22:58:00 -
[6] - Quote
Indalecia wrote:TheSkeptic wrote:Store the fittings as ship DNA Then when displaying the information just parse the fitting DNA. http://wiki.eveonline.com/en/wiki/Ship_DNAEdit: another advantage is you can then create pages for the IGB with the fitting links that when clicked should open the fitting in game This page is completely wrong anyway, the format does not work like this at all. I don't reccomend storing DNA in the database either, because the format is incomplete, ambiguous and sparsely defined at best. There is no silver bullet to this problem. Either you have to go with lots of tables and foreign key constraints to do it "properly", or you could store something else in the table (storing the CLF form comes to mind, although you lose the ability to write smart queries to pull specific data out of it).
Uh, ship dna does work in the way the wiki page says.
ship id (which can be shipid followed by the typeids of the subsystems, seperated by semicolons) then the highslot modules in the form: typeid;number of them, with colons between the different modules. Then the medium and lows in the same way.
It's a nasty format for parsing, as it doesn't actually delimit each 'section' in a different way from the internal delimitations (hence why the the thing I wrote does all the parsing in php, with a db connection available, ignoring the actual order) But it works, with the IGB link.
Storing the DNA does mean you don't need to create it on the fly, when you want the link. Is it the only way you should store it? no. but it's handy to keep as a bit of meta data. typeIDs don't really change. FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Indalecia
69
|
Posted - 2012.11.16 23:51:00 -
[7] - Quote
Well here's an example of how bad it is.
Quote:SHIP -> SHIP_TYPE_ID ( ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ) This is not how it works. If you follow this rule, the game will not parse your DNA correctly. According to this rule, here would be a valid proteus DNA:
Quote:29988:29981:30062:30151:30130:30098:3146;4:28756;1:11578;1:5975;1:527;1:448;1:11325;1:18813;1:14072;2:10190;3:31059;3:2456;5:23707;10:2488;5:12789;4:30486;1::
This doesn't work. You should use this instead:Quote:29988:3146;4:28756;1:11578;1:5975;1:527;1:448;1:11325;1:18813;1:14072;2:10190;3:31059;3:29981;1:30062;1:30151;1:30130;1:30098;1:2456;5:2488;5:23707;10:12789;4:30486;1::
Quote:DNA -> SHIP ':' HIGHS ':' MEDS ':' LOWS ':' RIGS ':' CHARGES This line isn't exactly right either, for example if you wanted a fit with only rigs, this rule tells you to use "typeid:::::rigid;qty:" but this is not how the game generates it (also misses the "::" terminator generated on all fits, even those with rigs and charges).
And this doesn't even cover the ambiguity caused by the format itself, such as find out where the charges are supposed to go. |
Steve Ronuken
Fuzzwork Enterprises
788
|
Posted - 2012.11.17 00:11:00 -
[8] - Quote
Indalecia wrote:Well here's an example of how bad it is. Quote:SHIP -> SHIP_TYPE_ID ( ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ':' SUBSYSTEM_ID ) This is not how it works. If you follow this rule, the game will not parse your DNA correctly. According to this rule, here would be a valid proteus DNA: Quote:29988:29981:30062:30151:30130:30098:3146;4:28756;1:11578;1:5975;1:527;1:448;1:11325;1:18813;1:14072;2:10190;3:31059;3:2456;5:23707;10:2488;5:12789;4:30486;1:: This doesn't work. You should use this instead: Quote:29988:3146;4:28756;1:11578;1:5975;1:527;1:448;1:11325;1:18813;1:14072;2:10190;3:31059;3:29981;1:30062;1:30151;1:30130;1:30098;1:2456;5:2488;5:23707;10:12789;4:30486;1:: Quote:DNA -> SHIP ':' HIGHS ':' MEDS ':' LOWS ':' RIGS ':' CHARGES This line isn't exactly right either, for example if you wanted a fit with only rigs, this rule tells you to use "typeid:::::rigid;qty:" but this is not how the game generates it (also misses the "::" terminator generated on all fits, even those with rigs and charges). And this doesn't even cover the ambiguity caused by the format itself, such as how to find out where the charges are supposed to go.
Funny. The first one you're using worked absolutely fine for me.
Third on the following page (it's using the dna to ship fitting display code I've got. but that doesn't edit the dna. just shoves it into the javascript call) http://www.fuzzwork.co.uk/ships/test.html FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
Indalecia
69
|
Posted - 2012.11.17 09:55:00 -
[9] - Quote
Quote:the game will not parse your DNA correctly. http://i.imgur.com/Zw1Ja.png |
Steve Ronuken
Fuzzwork Enterprises
789
|
Posted - 2012.11.17 17:20:00 -
[10] - Quote
Umm.
I could have sworn it came out in game ok for me. It appears, however, that I'm wrong.
Sorry about that. FuzzWork Enterprises http://www.fuzzwork.co.uk/ Blueprint calculator, invention chance calculator, isk/m3 Ore chart-á and other 'useful' utilities.As well as mysql and CSV/XLS conversions of the Static Data Extract. |
|
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |