The best way to structure this database?

Question

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.

Answer 1

Questions to ask yourself:

1. Is there a 1 to 1 relationship between gem, effects, and source?
2. Would you more often be pulling effects without pulling data from gem?

If the proposed tables have a 1 to 1 relationship then I'd suggest leaving them combined in one table. The only time I would consider splitting them out in this condition is if I only needed data from effects without needing other data AND these tables were going to be large enough to justify having them stored on different drives. Otherwise, you're just making work for yourself, adding more storage requriements and reaping exactly zero benefits.

Answer 2

You should also consider whether you will need the effects information for actual usage, or display only. If it is display only, no big deal to have it in one column in a table. If you have to use it, for example to apply the +12 and +10 appropriately, then I think you should put each occurrence of it in a separate column. Accordingly, you should have a separate table for effects, and then a separate table storing which gems have which effects, maybe gemeffects. The Effects table might have better descriptions of what "sp" stands for, maybe the min and max ranges, etc. The GemEffects table would just have the gem id, the value, and the effect itself. For example

Effects

effect  => hit
desc    => How many hit points
minimum => 0
maximum => 100

GemEffects

id     => 40153
effect => sp
value  => 12

and

id     => 40153
effect => hit
value  => 10

Answer 3

I happen to be passingly familiar with the environment you're describing (:)) Despite what you have convinced yourself, what you are doing is not particularly complex.

Anyway, currently, you have a table with no relationships. It's simple. It's easy. Each gem exists in the database.

If you were to move to the three tables that you proposed, you would also need to include link tables to assemble the tables into useable data, especially since (and mind, I'm not quite sure how your distinctions boil out) the effects and source table are involved in a many-to-x relationship: each gem has up to two effects, and each effect has up to Y gems where it is present // each source has up to Z gems.

I'd stick with the single table. The individual records may be longer, but its much simpler, and you'll encounter fewer errors than if you were trying to establish linking tables or the like.

Answer 4

You would answer your own question if you do a simple exercise: describe in a natural, descriptive language your system. Which entities, their attributes, how they interact with other entities, etc. Underline substantives and verbs. Ask what entities do you mean to manage (eg: will there be an interface to manage the "effects" table?) You'll be surprised how it all gets assembled naturally.

Now for your example, I'd suggest two approaches (without syntactic details)

1) to gain experience in relational design, with some complexity overhead, and granular over each entity

• gem (id, name,color_id,source_id,effect_assoc_id)
• color (id, name)
• source (id, name)
• effect (id,value,nature_id)
• nature (id, name)
• effect_assoc (id, gem_id, effect_id)

2) straight to the point, possibly valid depending on the cardinality of your relations

just carry on ;)

From your description, I'd go with #1.

Answer 5

I would recommend the following:

1. Move all effects into their own table (e.g., ID, Name, Description, Enabled, ...)
2. Move source into its own table (e.g., ID, Name, Description, Enabled, ...)
3. Drop gems "effects" column (migrates to step 5 below)
4. Convert the gems "source" column into a foreign key value that corresponds to the PK from the "source" table
5. Add a new table to link a single gem entity to zero or more effect entities
         Example: tbl_GemsEffectsLink, with two columns named "GemID" and "EffectID," that by
         themselves are foreign keys back to the entity tables and when taken together, make up the
         composite primary key.
        
         A sample view of this link table would be as follows:
                   

         GemID EffectID
           1      1
           1      2
           2      1
           2      2
           2      3
   

So, in summary, you would have the following tables:

1. gems
2. effects
3. source
4. gemseffectslink

With each table having the following columns:

1. gems
         id (PK)
         name
         colour
         level
         sourceid (FK)
        

2. effects
         id (PK)
         name
         description
         enabled
         ...
        

3. source
         id (PK)
         name
         description
         enabled
         ...
        

4. gemseffectslink
         gemid (FK)
         effectid (FK)
        

Lastly, this assume each gem can have zero or more effects, a single source (you can enforce NULL or NOT NULL for this gem.sourceid FK field), and that the level integer value is just that (i.e., not representing something more robust and exhaustive in that there exists some type of "Level" entity and the value of "80" in your sample data row uniquely identifies one of these "Level" entities).

Hope this helps!
Michael