   [web] How would you organise this data?
  Related to the same project as my more recent post...I have to store information about a treausure map in my sql database using tables and such.- There can be any number of maps, each with their own PNG file.- Each treasure hunt uses one png file and 10 clues, each clue has up to 3 further hints.- There can be any number of authers who can author any number of hunts.This is what i came up with:users table:columns - username, password, first name, last name, logged_in // THIS IS IMPLEMENTED AND IS FINEmapfiles table:columns - author(username), map title, server-side path of pictar to be loadedNow the problem arises when i wonder how to organise all the clues.a) Should i have a table for each treasure hunt? This would have columns:clue1hinta,hintb,hintcclue2hinta,hintb,hintc...giving you an aweful number of columns and limiting the number of clues and hints.b) have a table for all cluesc) have a table for all hintsd) somehow associate these two tables with the hunts. When a hunt is loaded, the map file is loaded as referenced in table mapFiles and then both the clues and hints are looked through for clues and hints associated with said hunt.Can anyone throw a few spanners here and think of a better way or organising this?Thanks,Dave   
  Table hunts:primary_key: int hunt_id;string hunt_png_file;string author;// additional fieldsTable Clues:primary_key: int clue_id;int hunt_id; // the hunt to which an entry in this table is associated// whatever clue dataTable Hints:primary_key: int hint_id;int clue_id; // the clue to which an entry in this table belongs.// hint data   ;  I suppose if i know that there will be 3 hints per clue, or at least if i limit it to that , i could put the hints with the clues.   ;Quote:Original post by DaveI suppose if i know that there will be 3 hints per clue, or at least if i limit it to that , i could put the hints with the clues.You could, but I think it's better to have them in their own table:1. You can easily change your mind later and have more (or less) hints.2. You can have only 1 hint for clue A, and 3 hints for clue B etc. without having a lot of empty fields in table rows.   