StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, interesting, why do you want to use 3NF?</p> <p>In MVC web frameworks (like Codeigniter, Zend Framework or Ruby on Rails) you don't require 3NF or even care about the different tables. What you're trying, I'd say, is multiple table inheritance, which is not very common.</p> <p>So actually you have a, say, <code>Custumer</code> class, which has all the attributes you have your Customer, User and Person table. To stick to MVC and the <em>thin controller</em> approach, you'd have a <code>save</code> action in your <code>CustomerController</code> class which accepts these attributes and instantiates an object from this <code>Costumer</code> class:</p> <pre><code>class CostumerController { function create() { Costumer c = new Customer(); c.save($_POST['costumer']); } } </code></pre> <p>(Make sure, you check the parameters for SQL injections but your framework should handle that somehow.)</p> <p>Your customor class should have a constructor which should accept all its attributes and save them to the appropriate tables. I.e.:</p> <pre><code>class Costumer { function save(params) { $sql = 'INSERT INTO person SET `phoneNumber` = "' . params['phonenumber'] . '"'; $dbh->query($sql); $lastid = PDO::lastInsertId; $sql = 'INSERT INTO user SET `password` = "'. md5(params['password']) . '", `personId` = "' . $lastid . '"; ... } } </code></pre> <p>As you may have noticed, I've shortened the Sql statements and some of the code since I don't know CodeIgniter. But the idea is to give all the data you need to the save method which saves it to the appropriate tables in you database. <strong>Remember:</strong> Only the model class communicate with the database in MVC.</p> <p>The problem is the ID you use as a foreign key in other tables. So after inserting your data to person you need to query the table for the ID it just inserted and use it in another table.</p> <p>That is why some frameworks (so to say: <em>all I know</em>), use single table inheritance by default. Picture it like this: </p> <p>The way you described it, your <code>Costumer</code> inherits from <code>User</code> which inherits from <code>Person</code>. All these tables are merged into one table, which also holds a <code>type</code> attribute, which says which type that row's object is, in our case: <code>Customer</code>, <code>User</code> or <code>Person</code>. If you only add a user, all unused attributes will be set to <code>NULL</code>. But this would break your 3NF, of course.</p> <p>What I don't understand, though, is why you always require 1:1 relationshipts. For example between <code>Person</code> and <code>Company</code>. Oh, now I get it, since there is no <code>companyId (FK)</code> in <code>Person</code>, many <code>Person</code>s can be in one <code>Company</code>. But why does <code>Branch</code>have a <code>personId (FK)</code>. Usually there are more person working in a branch, no?</p> <p><strong>Back to your question</strong></p> <p>You can either use <em>thin-controller-fat-model</em> or <em>fat-controller-thin-model</em>, though I prefer the first one. In this, you'd only have a few lines in your controller and more logic in your model (as in my example above). The communication with the database happens only in your model classes. You usually don't have a one-table-equals-one-model-type approach, but you may also have more than one model type accessing one table (see the single table inheritance thing).</p> <p><strong>Another approach</strong></p> <p>Your could have a <code>Costumer</code>model that accepts your data and "group" it the way your tables are "scattered":</p> <pre><code>class Customer { function save(params) { Person $person = new Person(); $person.save(params['person']); User $user = new User(); $user.save(params['user'], $person->id); .... } } </code></pre> <p>But... well, as I said, I'd suggest you leave the 3NF approach and use STI (single table inheritance).</p> <p><strong>Don't forget</strong> to make sure to quote form data to avoid SQL injections or see, which functions your framework offers to do that for you.</p> <p>Sorry for any mistakes in the code, I was only "coding" from memory and didn't even syntax parse it.</p> <p>HTH</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload