0

Currently my app creates tables for each customer in which they can store their own contacts.

This is created on the fly for each customer as they are created themselves.

eg: Customer 1 get a new table created called contacts_1

I have an ORM model called Contact, which i have adapted slightly to allow either one or two variables to be passed to when using the factory.

eg: Load the Contact object as an iterator for that list $contact = ORM::factory('Contact',1);

eg: Load the Contact object for a specific contact $contact = ORM::factory('Contact',array(1,1)); (First variable is the list id, second the contact id)

This all works fine, however when trying to save a contact's info when loaded via the iterator, it's not passing the list_id variable through, so cannot perform the create / update correctly.

It works fine when loading a single contact however.

I'm looking for suggestions / advice on how to either: * Do this better, short of creating individual ORM models for each of the contact lists created on the fly * How to pass the list_id variable through to the iteration section of the ORM model, so the save function performs correctly.

GoldieNZ
  • 53
  • 7
  • This is not how you design databases. You should use one table, and add a `customerId` column to it (so you can find contacts that belong to the customer). Spreading data out into tables like you are doing is messy. Keeping everything in one place enables you to reason about it. – Sverri M. Olsen Oct 15 '15 at 21:45
  • Thanks for your feedback. The challenge with this approach is customers are able to add their own extra columns for extra data, outside of the standard ones assigned during creation. This leads to differing schemas, and it seemed the best way to achieve and allow this was separate tables. Would you still suggest the same approach knowing this? With this approach, would i store the extra data in separate tables (or a single table)? – GoldieNZ Oct 15 '15 at 21:54
  • 1
    SQL does not do arbitrary stuff (such as custom columns) very well. For that you would use something like a NoSQL database. You *can* do it with SQL, but as the database grows it is eventually going to end up a mess. I would probably create two separate tables, `contact_extra_types(id, typeName)` and `contact_extra_data(id, customerId, typeId, value)`. That way you can create as many custom types as you want, and allow them to add as many custom fields as they would like, while still keeping a sane schema. – Sverri M. Olsen Oct 15 '15 at 22:11
  • That's brilliant! I'd never thought of approaching it that way, and you're right, it would bring a whole lot of sanity back to what is quickly becoming quite insane! THANK YOU – GoldieNZ Oct 15 '15 at 22:15
  • @SverriM.Olsen `(custeromerId, typeId)` should suffice as primary key of `_data` table (no need for yet another id). But to be honest I don't know how well Kohana handles that – kero Oct 16 '15 at 14:35
  • @kingkero I *always* add `id` fields to tables. You never know if you are going to need it at some point. Also, I believe Kohana's ORM requires a unique `id` field (it has been some time since I have used Kohana, so I could be wrong). – Sverri M. Olsen Oct 16 '15 at 15:54
  • 1
    @SverriM.Olsen I understand where you are coming from. But from a DB perspective, an id currently makes no sense and can even be hurtful. `roles_users` has no id (only used as through table, so no problem in Kohana). If you set userId and typeId as primary key, you can guarantee that no user has two values for the same field - if they are not the pk, you'd need to set an unique constraint on the pair – kero Oct 16 '15 at 16:15
  • @SverriM.Olsen is correct about Kohana and the ORM models needing the primary key to be a field named "id" unless you override that in the model such as "protected $_primary_key = 'strange_pkey';" – pogeybait Dec 03 '15 at 05:40

0 Answers0