Databases
- Thread starter Matt123
- Start date
I use MySQL regularly, at work and at home. As a relational database, it frees you up from having one massive all-inclusive redundant table by splitting common data up into subordinate tables. Thus instead of needing 128 fields you may only need, oh, a dozen or so fields, several of which will index into another table with more detail.
So for example, create a table of ships. Then create a table of weapon types and their characteristics. Then create a table where each row relates a ship to a type of weapon battery (and perhaps the status of said battery for that ship).
And so on.
Hmm, sounds like I've been trying to build a monolithic block of data and you guys are suggesting there are more sophisticated ways of doing it.
I'm a student that likes to dig up and express what I think is the likely solution and then have it adjusted by others. Feel free to adjust...
The purpose of this database is to store the detail of HG Ship Designs submitted by gamers.
Skimming articles on Relational Databases, the gist as I see it applies to HG, is that I'm trying to use the format to create a 2 dimensional table when its designed as 1 dimensional table (information in one row, vs HG Ship Designs holding information in 3, 4 or more rows). And uses relationships to link those tables together.
A solution would be to create seperate tables for each piece of information relating to component parts. For example tables for component Codes, Number, Hp, Ton, Mcr, Ep, Crew.
Tables would look like;
Component..........Code
ShipID
JumpDrive..........3
PowerPlant........4
Component...........Ton
ShipID
JumpDrive..........800
PowerPlant........1100
etc
I considered a seperate table for weapons instead of component features, but a weapons table seems to still be inherantly a two dimensional table.
Am I heading down the right path?
I'm a student that likes to dig up and express what I think is the likely solution and then have it adjusted by others. Feel free to adjust...
The purpose of this database is to store the detail of HG Ship Designs submitted by gamers.
Skimming articles on Relational Databases, the gist as I see it applies to HG, is that I'm trying to use the format to create a 2 dimensional table when its designed as 1 dimensional table (information in one row, vs HG Ship Designs holding information in 3, 4 or more rows). And uses relationships to link those tables together.
A solution would be to create seperate tables for each piece of information relating to component parts. For example tables for component Codes, Number, Hp, Ton, Mcr, Ep, Crew.
Tables would look like;
Component..........Code
ShipID
JumpDrive..........3
PowerPlant........4
Component...........Ton
ShipID
JumpDrive..........800
PowerPlant........1100
etc
I considered a seperate table for weapons instead of component features, but a weapons table seems to still be inherantly a two dimensional table.
Am I heading down the right path?
If the database is intended for use as the backing-store for a single-user application, you might consider using SQLite. SQLite is very lightweight, capable and fast. There is a useful add-on for Firefox that can be used to manage SQLite databases: SQLite Manager.
As to database structure for High Guard ship designs -- that's a nice little brain teaser for a lazy Sunday morning (hmmm, Sudoku or Traveller noodling? no contest!). A toy schema that reflects my gut reaction is captured in SQLite syntax on pastebin.
As to database structure for High Guard ship designs -- that's a nice little brain teaser for a lazy Sunday morning (hmmm, Sudoku or Traveller noodling? no contest!). A toy schema that reflects my gut reaction is captured in SQLite syntax on pastebin.
There are a number of ways you could go. Off the top of my head I’d start with 3 tables for holding designs and 2 reference tables:
Header table (ID number is key):
ID number | Name | Displacement | TL
VariableComponents table (ID number + VC number is key):
ID number | VC number | VC Component ID | Percentage
FixedSizeComponents table (ID number + FSC number is key):
ID number | FSC number | FSC Component ID | Quantity
VariableComponent Reference table (VC Component ID is key):
VC Component ID | Component Name | TL | Cost per dton | EPs per dton
FixedSizeComponent Reference table (FSC Component ID is key):
FSC Component ID | Component Name | TL | Displacement | Cost | EPs
So, once the reference tables have been populated with all possible componetents, then a design could be recorded as one header table row plus a number of VC table rows and a number of FSC table rows. All VC Component IDs on the VC table must exist on the VC reference table (this is called a foreign key), all FSC Component IDs on the FSC table must exist on the FSC reference table (this is another foreign key).
This is just a starting point. I'd recommend you pick up a book on SQL (Dummies Guide or whatever) ... you need to know 'table', 'row', 'column', 'primary key', 'relation', 'foreign key' (and possibly 'normalisation' or 'third-normal form').
Header table (ID number is key):
ID number | Name | Displacement | TL
VariableComponents table (ID number + VC number is key):
ID number | VC number | VC Component ID | Percentage
FixedSizeComponents table (ID number + FSC number is key):
ID number | FSC number | FSC Component ID | Quantity
VariableComponent Reference table (VC Component ID is key):
VC Component ID | Component Name | TL | Cost per dton | EPs per dton
FixedSizeComponent Reference table (FSC Component ID is key):
FSC Component ID | Component Name | TL | Displacement | Cost | EPs
So, once the reference tables have been populated with all possible componetents, then a design could be recorded as one header table row plus a number of VC table rows and a number of FSC table rows. All VC Component IDs on the VC table must exist on the VC reference table (this is called a foreign key), all FSC Component IDs on the FSC table must exist on the FSC reference table (this is another foreign key).
This is just a starting point. I'd recommend you pick up a book on SQL (Dummies Guide or whatever) ... you need to know 'table', 'row', 'column', 'primary key', 'relation', 'foreign key' (and possibly 'normalisation' or 'third-normal form').
Sudokus are too easy... ;-)
Unless they're from the original Japanese company which perfected Sudoku years ago... Nikoli Publishing.
(I DON'T do any of their puzzles in ink... All their puzzles are made by hand...)
lol, more reading to do...
Thank you for the outline. I've read enough to follow it, but not enough to use it, yet...
Your format is similar to several spreadsheets & a Perl application I once knocked up. I can see a few benefits if I can get on top of this.
Cheers!
Matt
as said, best way is to normalize the data - multiple tables to handle things more generically. And there are all sorts of ways of doing this, so have fun.
As for the actual DB, there are several free ones as also mentioned: SQL CE (compact edition), MySQL are the 2 biggest ones probably. Although I'm more MS oriented these days, I did spend a year using MySQL and there are several advantages to that DB for ease of use (although there may be command line options for the various MS DB stuff, the MySQL, as it runs on more operating systems, seems to have a very robust command line interface. But you can also always use things like Toad to get a GUI front end on it as well)
As for the actual DB, there are several free ones as also mentioned: SQL CE (compact edition), MySQL are the 2 biggest ones probably. Although I'm more MS oriented these days, I did spend a year using MySQL and there are several advantages to that DB for ease of use (although there may be command line options for the various MS DB stuff, the MySQL, as it runs on more operating systems, seems to have a very robust command line interface. But you can also always use things like Toad to get a GUI front end on it as well)
