[Newbie] Help with model design

[SpeedJet]

Hello Forum!

I'm starting with Ruby and RoR from zero, read almost every news, doc and tutorial for the past 2 weeks and installed just monday to start modeling my webapp.

The thing is this: my background on DB and SQL sucks... really. Just created a few php-hand-coded webapps but nothing so rational and so objective as Ruby (and Rails) force me (in the good way, of course).

Start designing in php a webapp for managing TV ads breaks (these ones you see in the middle of a tv show).

What I want to achieve is create it 100% on Rails.

based on the objective/rational model, I have a few/bunch of channels, each one have N breaks with M number of ads on them.

So if I do:


ch = Channel.find(1)

# check if have some breaks on it:
if ch.breaks.length > 0

# list each ads on each break:
for break in ch.breaks
puts "break starttime:" + break.starttime
for ad in break.ads
puts "ad id:" + ad.id +", on break id:" + break.id + ", position:" + ad.position
end
end


Because ads are reusable, the position of each ad on the "playlist" may vary, so cannot store it on the ads table of my DB, right?

so:

Channel model:
has_many :breaks

Break model:
belongs_to :channel
has_many :ads

Ad model:
has_and_belongs_to_many :breaks, :order => "position DESC"


I'm in the good path, right?

Now, the worst part is translate this to a SQL, so the tables are easy to setup, and the join_table of breaks and ads will looks like:

breaks_ads
break_id
ad_id
position (INT)

the two _id fields are ForeignKeys (FK)...

Now... how to create a decent SQL from this? I really don't understand FK stuff... complex queries (aka: unoptimized mosters) I have done for PHP.. but now this is new for me :confused:

Any help?

I'll really apreciate any comment on this.

Please excuse my poor english.

Luis

[bluetechnx]

The db schemas look ok and there doesn't appear to be anything out of the ordinary.

I don't exactly understand if you have done SQL queries in the past or not? Do you know SQL?

So, in short it looks ok. If you are going to store ads (videos) on the DB you should store it as a BLOB object.

An Ad could be the primary key and you could reference what channels and breaks a specific ad is in. This could be done in SQL (either nested queries or subselects)

[edit:]lemme look at my SQL book and I'll see if I can post a simple "complex" query to do this and I'll post back

[SpeedJet]

I don't exactly understand if you have done SQL queries in the past or not? Do you know SQL?


Actually, I didn't.

I was looking for some mysql references to understand referencing and foreign keys.

So this database model is in my head, trying to figure out the FOREIGN KEY, REFERENCES and ON [UPDATE/DELETE] NO ACTION / CASCADE... :confused:

In my case, deleting a record from Ads should remove it from the breaks that reference it, right?

So...

class Break < ActiveRecord::Base
belongs_to :channel
has_and_belongs_to_many :ads
end



class Ad < ActiveRecord::Base
belongs_to :advertiser
has_and_belongs_to_many :breaks, :order => "position DESC"
end



CREATE TABLE `ads_breaks` (
`break_id` int(11) unsigned NOT NULL default '0',
`ad_id` int(11) unsigned NOT NULL default '0',
`position` int(11) unsigned default '0',
FOREIGN KEY (`ad_id`) REFERENCES `ads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`break_id`) REFERENCES `breaks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


I needed to change the table name due the "lexical" order of tables ("a" came before "b", duh).

So now I could call:


Break.create(:ads << Ad.find(1), :ads << Ad.find(2))


And that will automatically fill the join table...

To delete


Ad.delete(1)


Should delete every reference to that Ad on each Break, right?

Now I need to find a way to "automatically" guess the new position of the ad on the break (for the position field).
Auto increment it or take the top value from it, plus 1.

maybe using the before_create and before_update? but... on which model?

Sorry if I self-answer my questions, but sometimes "rethorical" questions are very useful...

Thank you for your time reading this and helping a newbie get dirty into rails ;-)

Regards,

Luis

[bluetechnx]

you may either of these two sites useful, these are links provided by my DB professor I took laster quarter at school.

An SQL tutorial:
http://bioinfo.mbb.yale.edu/~mbg/clippings-u/sqltut.htm

Some stuff on triggers and constraints:
http://oldweb.uwp.edu/academic/mis/baldwin/cnstrnt.htm

I was looking for another tutorial/reference site I onced used but some of my instructors links were broken :(.

To me these two sites appear to be pretty useful for the problem your looking at.

[SpeedJet]

you may either of these two sites useful, these are links provided by my DB professor I took laster quarter at school.

An SQL tutorial:
http://bioinfo.mbb.yale.edu/~mbg/clippings-u/sqltut.htm

Some stuff on triggers and constraints:
http://oldweb.uwp.edu/academic/mis/baldwin/cnstrnt.htm

I was looking for another tutorial/reference site I onced used but some of my instructors links were broken :(.

To me these two sites appear to be pretty useful for the problem your looking at.

Thank you bluetech!

Found the info I was looking for (just checking the places you post) and a few try and error tests.

WoW!

After you have "drawn" the schema, create a ruby app is almost as ridding a bike...

Solved in 48 hours what took me weeks with php (I didn't mention that the first version of this app was made with zope, hehehe).

So... Still need to figure out a few things, like date/time handling for validation, and the right db-fields for them.

But thats another topic :cool:

Thank you for the time you took to answer this message.

Regards,

Luis