CI, MVC and m:n table-relationships

[eluser]eff_kay[/eluser]

Why do you use composite keys for your joining tables normally? I mean, you aren’t using natural or composite keys for your other tables here - just curious on the rationale.

Well, for one thing, I use a visual database designer app which lets me produce ER-diagrams like the one I included above. If I make a m:n relationship between two tables, a new table is automatically created with the two FKs as a composite PK.

I always use a surrogate PK for normal tables. I'm not willing to rely on say, scenario name to be unique for instance. In the "x_has_y"-tables (I dont know what they are actually called), the combination of the two FKs should ALWAYS be unique. If not, something is wrong. If I were to use a surrogate PK for the "x_has_y"-tables, a combination of the FKs could potentially appear in multiple rows since they're no longer the PK and doesn't need to be unique. The error that produces multiple rows of the same FK-combination is thereby veiled. Using a composite PK here disallows multiple entries to contain the same FK-combination.

I don't know if that made sense, but it does to me. :c)

[eluser]jedd[/eluser]
Ah, okay. So you'd never do a lookup for scenario.id based on scenario.name?

I like the idea of using the DB table's constraints to enforce some sanity of your data. Obviously I do that enforcement in the code only, though defence in depth and all that. Shame there's not a way to apply a UNIQUE force-field around multiple columns.

[eluser]eff_kay[/eluser]
@jedd:
No, the scenario id will always be provided.
Well. As far as I know, the only way of telling mysql that a combination of indeces should be unique, is by setting them as a composite PK. I could always expand the PK in my join-tables to include the surrogate key you suggested, but that would just be pointless, really.

I have another question regarding organizing of code. I have seen some people formatting queryresults inside the model before passing it back to the controller. E.g. putting a resultarray into an <ul>. Until I saw this, I thought that this kind of thing, this 'html-i-fying' of otherwise raw data, was what you would call view-logic and that such things belonged in the view. Was I incorrect in that assumption? If I was, the only thing I can think of that view-logic could be, is simply 'what data goes where' in the html.

Thanks in advance.

[eluser]jedd[/eluser]
Oh, I wouldn't advocate extending your key to three columns there. That'd just be silly, as you say.

For models - I do massage the data such that my controller has some predictability with the layout of same when it's returned. But no, I'd never put any markup entities into data coming out of the model. I'm loathe to do that with my controllers, now, and have started to strip out all CI's table helpers (etc). I do handle HTML snippets, as variables that get assigned in my MY_Controller, and then passed down to controllers that then bump them straight out to views, but I don't see a) anything wrong with that, and b) any way around it (but that's moot because of (a) of course

So, yes, I think your original assumption was right, and you should probably eschew that kind of behaviour.