More Database Design

[daniel2335]

There are 3 tables ships, battlegroup, battle_ship.

Each user has 4 battlegroups and each battle group can contain any of the available ships and various quantities of those ships.

Ships
ShipID PK
Armor
Shields
etc
etc

BattleGroup
BattleGroupNum PK
UserID PK/FK
Location
Destination
Strategy

Battle_Ship
BattleGroupNum PK/FK
UserID PK/FK
ShipID PK/FK
Quantity

I'm not sure about if using a compesite key is the best thing to have linking in a many:many.

As if 1000 user each has 4 battlegroups with each battle group having 20 different types of ship in, then thats 80000 rows in Battle_Ship table so less fields the better in there?

If I have a single pk link it would mean an exta field in BattleGroup table. Eg

Ships
ShipID PK
Armor
Shields
etc
etc

BattleGroup
BattleGroupID PK
UserID FK
BattleGroupNum
Location
Destination
Strategy

Battle_Ship
BattleGroupID PK/FK
ShipID PK/FK
Quantity

Thanks in advance.

[Annalyzer]

I don't know the "right" answer here, but I would base my decision on how I was going to refer to the Battle_Ship in future code. For example, say Fred is the player and Fred has the following battlegroups: BG1, BG2, BG3, and BG4. If BG1 contains an aircraft carrier (AC), a cruiser (CR), and 2 destroyers (D1 and D2), then it would make more sense to me to write code that used BG1/AC to refer to the aircraft carrier in battlegroup 1 and BG1/D2 to refer to the second destroyer in battlegroup 1 than to use some arbitrary primary key that didn't describe which ship/battlegroup I was really talking about.

Of course, I get lost easy so I like to use naming conventions that mean something later on down the road. =)

[daniel2335]

I think that will create more work as if there are 20 types of ship then their would be 20 many:many tables and Id have to query all 20 of them to discover all the ships BG1 has.

Humph database design is so difficult :p

[Annalyzer]

No, that would make 1 table with 20 records.

[Atli]

@Annalyzer
This is all dynamic, so there should never be any need for you to actually refer to the data by ID (or a name) in the code.

All you would be doing, by using "BG1/D2" to refer to a battle-group<->ship relationship, is replace two 8byte integers with varying amounts of text, which will increase it's size and query times considerably without any gain at all.


The first design posted is pretty close to what I would do.

Except, I would not include the UserID in the BattleGroup table primary key.
Ideally, the PK would be a Auto_Increment'ed integer, and the UserID a foreign key integer.

I would also remove it from the link table (Battle_Ship).
As the UserID is already in the BattleGroup table there is no need to include it here. The tables can easily be joined if there is need for that.

So, something more like:

 Ships
  ShipID PK
  Armor
  Shields
  etc...
  etc...
 
 BattleGroup
  BattleGroupID PK
  UserID FK
  Location 
  Destination
  Strategy
 
 Battle_Ship
  BattleGroupID PK/FK
  ShipID PK/FK
  Quantity
 

This would obviously allow for more than four groups per user, but that sort of limitation should be enforced by the application code anyways, not the database.

Storing the battle-group number would be redundant, as you could just count the groups owned by a user ID to get the total count, and select them ordered by the group ID to get them in order. (Which your application could then assign indexes to, based on the positions in the result table... or simply use the given ID returned by the database.)

[daniel2335]

The UserID was in the link table as it was part of the composite key but it didnt feel right doing it like that.

The battlegroup numer may have to be added as im not sure if im going to allow dynamic create and destrustion of battlegroups which would mess up ordering by group ID.

Unless the user gets 4 rows created in battlegroup table on register and then they are hidden according to research and not deletable. Yeah that will work. :)

I am worried about the potential size of the link table assuming an optimistic 1000 users with 4 groups and 20 ships in each group. Thats 4000 rows in battle group but 800,000 in the link table. Left joining these (the approach i plan to take) to get the information will take a life time to search all those records.

Thanks for the design mod, it looks usable now :)

[Atli]

@daniel2335
What do you mean by this?
Why would the order of the group ID's matter?
@daniel2335
Wouldn't that be 80.000 rows?
1000 users * 4 groups * 20 ships == 80.000 entries.

Either way, I wouldn't worry about the size of the link table. By designing it to use only integer keys, even tho there are millions of records the size and query speed should remain relatively light.
Neither 800.000 nor 80.000 rows in the link table would cause any sort of performance or storage problem on any half-decent server.

[daniel2335]

If the BattleGroup ID's generated for a user are 1, 2, 3 that that relate to battlegroup 1, 2, 3. If I delete BattleGroup ID 1 then it will assign ID 2,3 to Battlegroup 1 and 2 but that should be 2 and 3.

Sorry made error in calculation.

I'm also thinking about adding another 2 fields to battle_ship to allow for production of ships (bquantity, start_time).
Which gives the build start time and quantity of building ships. When i come to use the table I can see how much time has passed since start time and the production time of each ship based on that I can move the approprate number of ships from build quantity into the normal quantity field and update the build start time. But they are all int values so should be all peachy.

The only problem with that is those two fields are redundant if there arnt ships being built. Other option would be to have a table very similar to battle_ship but that would probably make more work when it came to query as to find out how many ships there currently were I'd have to get the quantity from one table then find out if enough time has passed in the other table to have produced more ships.

[Atli]

@daniel2335
I don't follow your logic here.

If you create three groups for a user, and ID #1 in the table has already been used, then the three new groups would be assigned ID's 2, 3 and 4.

You don't pre-assign ID numbers and insert them into the table, you insert the rows and have MySQL assign them ID's.

... I'll come back to the other thing later. Got to run.

[daniel2335]

But if I was to allow the facility to delete and recreate battlegroups, and if battle groups are assigned in ID order. Then if I delete and then recreate battlegroup 1 because its ID is now after other battlegroups the user has it will assigned as a different battlegroup number and the others will be shifted along with what was battlegroup 2 becoming battle group 1. But I have decided not to allow deletion and creation of battlegroups.

I think im also going to need another table as I was speaking against above because it is probably best if ships are built into a default area and not a battleground as these may not be at home. The user can then move ships to battlegroups.

Ships 
  ShipID PK 
  Armor 
  Shields 
  etc... 
  etc... 
 
BattleGroup 
  BattleGroupID PK 
  UserID FK 
  Location  
  Destination 
  Strategy 
 
Battle_Ship 
  BattleGroupID PK/FK 
  ShipID PK/FK 
  Quantity 
 
User_Ship 
  UserID PK/FK 
  ShipID PK/FK 
  BuildQuantity
  BuildStartTime
 
 

The new table would allow ships to be stored in a sort of limbo for the user to move around and would also hold the 2 fields required to track building of new ships.