Topic: help me slim down my database (for fun and profit?)

no, but you want to help anyway tongue

Lets say I have a Project model. The Project has one Leader, and many Employees.  There is also a Person model which has many Leaders and Employees (different roles the Person plays on different projects).  There is one other model called Tool.  Tool has many Employees and Leaders (the things they use on the project).  Also, the Employee and Leader have unique attributes for each project (think of them as roles on that specific project and what Tools they are using).

What I have done is made a Project model, Leader model, Employee model, Person model, and a Tool model.  What annoys me about this is there are loads of Employee entries since each project can have more than one employee obviously, and every new project makes new employees that essential point back to some of the same Person objects,  but I can't see anyway around it because an Employee has some unique properties for each Project.  Secondly, trying to find an employee's name by Project.Employee[index].Person.name is a bit much and not very DRY.  Another thing that is annoying is trying to find projects a person is related too.  For example, Person.Leaders.Projects obviously doesn't work.

I thought I had found an answer with polymorphic associations but really that only solved the data access issue.  It still doesn't address the ridiculous amount of employee records I end up with that really don't contain a whole lot of worthwhile data.

Any suggestions would be helpful.  I could be going about this completely the wrong way.  If you want more information, just ask.

Last edited by b4t3m4n (2007-03-14 18:20:04)

Re: help me slim down my database (for fun and profit?)

I like reading code. Would you mind posting the models with their relationships (has_many, belongs_to, etc.)?

Railscasts - Free Ruby on Rails Screencasts

Re: help me slim down my database (for fun and profit?)

Okay, well my example was a dumbed down version of what I am doing, might as well go all the way.

I am designing killboard for Eve Online.  Basically, when you kill someone in the game you get sent a notification mail like this..

2006.07.29 12:57

Victim: Ranged Airman
Alliance: Red Horizon
Corp: Red Horizon Navy
Destroyed: Blackbird
System: Passari
Security: 0.4

Involved parties:

Name: Itachi XIII (laid the final blow)
Security: -10.0
Alliance: Whips and Chains
Corp: Corsets and Carebears
Ship: Armageddon
Weapon: Tachyon Beam Laser II

Name: Chris3h
Security: -10.0
Alliance: Whips and Chains
Corp: Corsets and Carebears
Ship: Eagle
Weapon: 250mm Railgun II

Name: infraX
Security: -10.0
Alliance: Whips and Chains
Corp: Corsets and Carebears
Ship: Megathron
Weapon: 425mm Railgun II


Destroyed items:

Standard Missile Launcher I
Standard Missile Launcher I
Standard Missile Launcher I
Large Shield Extender I
ECM - Multispectral Jammer I, Qty: 3 (Cargo)
Capacitor Power Relay I
Capacitor Power Relay I
Rocket Launcher II (Cargo)
Defender I, Qty: 40


Every scrap of information on this mail is important and people expect to see it on such a website (there are currently some php boards in existence).  After examining the mail I decided I was going to need the following models:
Killmail
   has_one :victim, :dependent => :destroy
   has_many :killers, :dependent => :destroy

Victim
   belongs_to :killmail
   belongs_to :player
   has_one :destroyed_ship, :dependent => :destroy
   has_many :destroyed_items, :dependent => :destroy

Killer
   belongs_to :player
   has_one :used_ship
   has_one :used_item
   belongs_to :killmail

Used_ship
   belongs_to :killer
   belongs_to :ship

Used_item
   belongs_to :killer
   belongs_to :item

Destroyed_item
   belongs_to :victim
   belongs_to :item

Destroyed_ship
   belongs_to :victim
   belongs_to :ship

Ship
   has_many :destroyed_ships
   has_many :used_ships

Item
   has_many :destroyed_items
   has_many :used_items

Player
   has_many :victims
   has_many :killers


Now every single one of those models has some data associated with it, but not a lot.  For example, Destroyed_item just contains a quantity (int) of what was destroyed and the id to the appropriate Item model.  Killer keeps track of what ship, weapon, corp and alliance the player was in when the kill was made.  My problem with this system is I have loads of Destroyed_item table entries because, obviously, countless are made with every killmail.  It seems sort of pointless to go to all the trouble when all it contains is the quantity.  Maybe there is no way around it since even if it was Killmail has_and_belongs_to_many items, Item has_and_belongs_to_many killmails, I would still have a relationship table for the many to many.  It all just seems very heavy.

Not to mention, if I just want to know the quantity of a destroyed item on some killmail, I would need to do a somemail.victim.destroyed_items[x].item.name(would involve some function to search) then after finding it, would be another somemail.victim.destroyed_items[x].quantity to get what I want.  It gets ugly fast.

Maybe there is a better way to store and manipulate the data, completely open to suggestions, let me know if you want more details

Re: help me slim down my database (for fun and profit?)

b4t3m4n wrote:

My problem with this system is I have loads of Destroyed_item table entries because, obviously, countless are made with every killmail.  It seems sort of pointless to go to all the trouble when all it contains is the quantity.  Maybe there is no way around it since even if it was Killmail has_and_belongs_to_many items, Item has_and_belongs_to_many killmails, I would still have a relationship table for the many to many.  It all just seems very heavy.

If it's just used for sending mail, does it need to be stored permanently in the database. Can you just build the joins in memory temporarily for the mail and then not save it? If it needs to be persistent, I don't think there's any alternative since it's a join model and that information needs to be stored somewhere. How many records do you anticipate this will become? I wouldn't worry about it too much unless it will be in the hundreds of thousands/millions. Perhaps you can have a regularly occurring cleaner script to remove the old models to keep the database slim.

b4t3m4n wrote:

Not to mention, if I just want to know the quantity of a destroyed item on some killmail, I would need to do a somemail.victim.destroyed_items[x].item.name(would involve some function to search) then after finding it, would be another somemail.victim.destroyed_items[x].quantity to get what I want.  It gets ugly fast.

Have you looked into has_many :through? It's great for fetch models through an association.

class Victim < ActiveRecord::Base
  #...
  has_many :items, :through => :destroyed_items
end

Then you can fetch items with victim.items. If you need to go deeper I recommend creating a new method in the models to do the fetching, this helps keep the code much cleaner. I can give an example if you need it.

Railscasts - Free Ruby on Rails Screencasts

Re: help me slim down my database (for fun and profit?)

I think your a little confused which is probably my fault.  The mail I listed is what comes into the database.  It is pasted from the game client into a standard text box form and I parse it and store the data for stat keeping, mainly.

The amount of these mails that come in will be directly proportional to how popular the site becomes tongue  I don't know, lets say I would like to be able to support 10,000.  The most heavily used site has a million currently in their database so a 10k estimate isn't much.  Let's say the average killmail has 10 destroyed items, 5 killers and they all have 1 victim.  That is 16 rows, so 160,000 for just 10,000 mails.  I honestly don't know enough about database performance to know if that would begin to bog down something like mysql on a machine with 1 gig RAM and a 2.5 ghz processor.  Hopefully I have nothing to worry about.  Thanks for the help.

Re: help me slim down my database (for fun and profit?)

My guess is MySQL should have no problem handling this many records. Join tables are often very small too (only storing integers) so I doubt it will be a problem, especially if you add an index or two. However, I don't have much experience with very large databases. Perhaps someone else here does?

Railscasts - Free Ruby on Rails Screencasts