More Database Design

**Annalyzer** · Jul 17 '09, 03:57 AM

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** · Jul 17 '09, 07:45 AM

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** · Jul 18 '09, 12:55 AM

No, that would make 1 table with 20 records.

**Atli** · Jul 20 '09, 12:59 PM

Originally posted by Annalyzer

[...] 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.

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:

Code:

[B]Ships[/B]
  ShipID [B]PK[/B]
  Armor
  Shields
  etc...
  etc...

[B]BattleGroup[/B]
  BattleGroupID [B]PK[/B]
  UserID [B]FK[/B]
  Location 
  Destination
  Strategy

[B]Battle_Ship[/B]
  BattleGroupID [B]PK/FK[/B]
  ShipID [B]PK/FK[/B]
  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** · Jul 21 '09, 07:37 AM

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** · Jul 22 '09, 11:28 AM

Originally posted by daniel2335

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.

What do you mean by this?
Why would the order of the group ID's matter?

Originally posted by daniel2335

Thats 4000 rows in battle group but 800,000 in the link table.

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** · Jul 22 '09, 12:41 PM

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** · Jul 22 '09, 07:26 PM

Originally posted by 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.

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** · Jul 23 '09, 07:11 AM

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.

Code:

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.

More Database Design

More Database Design

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment