At the moment I'm doing this:
gems(id, name, colour, level, effects, source)
id
is the primary key and is not auto-increment.
A typical row of data would look like this:
id => 40153
name => Veiled Ametrine
colour => Orange
level => 80
effects => +12 sp, +10 hit
source => Ametrine
(Some of you gamers might see what I'm doing here :) )
But I realise this could be sorted a lot better. I have studied database relationships and secondary keys in my A-Level computing class but never got as far as to set one up properly. I just need help with how this database should be organised, like what tables should have what data with what secondary and foreign keys?
I was thinking maybe 3 tables: gem
, effects
, source
. Which then have relationships to each other?
Can anyone shed some light on this? Is a complex way like I'm proposing really the way to go or should I just carry on with what I'm doing?
Cheers.