Welcome Guest, Not a member yet? Register   Sign In
(n00b) My first lookup table !

Hi everyone,

Thanks for looking at my post. I'm trying to do a lookup table properly, with constraints and everything and am a little fuzzy on how I'm supposed to proceed.

I have three tables,

device(id, name)
outage(id, date, time)
device_outage(device_id, outage_id)

(please note, an outage can have many devices and a device can have many outages)

My first question is, do I need any foreign key constraints on this relationship. Also, is this called a many to many relationship?

Then when it comes to working with my CI Model, how can I get it to insert the data correctly? db->insert seems to only accept an argument for one table at a time, do I need to perform many queries to insert the data?

Thank you!

Would I be correct is assuming you're using MySQL with the MyISAM engine (default)?
If so, you don't really have much in the way of relational integrity (such as foreign key constraints) and transactions (Atomicity, so it all works, all the tables in a transaction get updated or none)

You might want to look into the InnoDB engine which give you these features, although I've not really used it much myself

And yes, it is called a many to many relationship, commonly annoted as m:n (instead of m:m)

And yes, db->insert is only means for a single table insert at a time, but if you use InnoDB, you can use the transaction support provided in the AR classes

[eluser]GSV Sleeper Service[/eluser]
you don't need foreign key constraints, they're nice to have, but you don't need them. yes, this is a many-to-many relationship.
If you're using the CI 'active record' class, then yes, you'll have to do multiple inserts in some cases, especially so when you're adding a new device and a new outage at the same time. I can't help any more here, I stopped using the CI Db class long ago.

[eluser]Evil Wizard[/eluser]
long live the Doctor Rine

[quote author="Evil Wizard" date="1246990454"]long live the Doctor Rine[/quote]

Huh? Is that something I'd need to be fully awake and functional to understand/appreciate?

[eluser]Evil Wizard[/eluser]
It's a play on words Wink Doctor Rine

[quote author="Dam1an" date="1246984146"]Would I be correct is assuming you're using MySQL with the MyISAM engine (default)?[/quote]

Yep, MySQL and nah, I found out that I need to use innoDB if I want to use foreign keys. I think they're a good idea at my level of coding...stops me doing something really dumb.

I'll check out transactions, thanks!

[quote author="Evil Wizard" date="1246990885"]It's a play on words Wink Doctor Rine[/quote]I don't want to learn doctrine just yet.. I just read about it, but learning CI is enough for my brain at the moment.. thanks anyway!

[eluser]Michael Wales[/eluser]
Edit: Doh! Should have researched this before I said it, MySQL does not support this.

Quote:You can use a join to insert across multiple tables at the same time.

Ok, so it's ok for my the function in my model to include three $this->db->insert lines?

I've got issues with this approach...

It will be really messy to insert using multiple lines because it will be 5 steps in total !

I first need to insert the device
Then insert the outage
then query the device table for the device id
then do the same for the outage id,
then insert the id's into the lookup table.

Is this the only way to do it?

Theme © iAndrew 2016 - Forum software by © MyBB