1

I am trying to creating a system where all new tables will be dynamically created (or objects inside a predefined databases.)

Here is an over view of the design, I would have a predefined table for

  • Objects (ie. object 1, 2, 3 ....n)
  • Fields (related to Object) field "ABC" is related to object (field 1 is linked to object 2....)
  • Data (ie. value for each field) so row 10 in this table is the value for field 1)

For example In the "objects" table I have a rows to look like this:

    object_id            object_name
    1                    accounts
    2                    users
...
...
...

In the fields table I will have the following rows:

    field_id         field_label           object_id
    1                Account Name          1
    2                Specialty            1
    3                DBA                   1
    4                First Name            2
    5                Last Name             2
...
...
...

Finally, the data will be captured in the Data table and it will look something like this:

data_id          value           field_id      object_id
1                Mike            4             2
2                Signs/Graphics  2             1
...
...
...

This design will allow me to dynamically create a new object, create fields inside this object, and finally capture data for each field. Everything will be done without having to create/alter tables at all.

Problem

The problem that I am having is, how would I generate a unique identified for each object? ( I need to be able to identified one row inside a dynamic object)

For instance, if I want to create 100k accounts "unique accounts" inside the accounts object. How can I automatically assign a unique ID for each row?

I should be able to create a new field and call it "account ID" in the field object and relate it to object_id 1. Then automatically populate an auto increment value inside that field. Since the field "account ID" is the primary identifier it should unique for each object.

I believe that I complete understand the design, but not sure how to auto generate unique identifier. The unique identifier will later be used to capture the data before storing it in the data object. The data object will then be something like this

data_id          value           field_id      object_id    object_unique_id
1                Mike            4             2            1
2                Signs/Graphics  2             1            1
3                Jack            4             2            2
3                Jay             4             2            3
...
...
...

row 1 of the data object will point to the field_id, object_id, and object_unique_id which represent the exact account that I the "First Name" field reference to.

I am using MySQL as DBMS and PHP as Scripting Language to accomplish this.

Any help will be greatly appreciated

Jaylen
  • 39,043
  • 40
  • 128
  • 221
  • Considered a document database? As for a primary key... use an auto increment? Not clear to me, but maybe you are saying you need a compound key, auto increment in conjunction with another column? – ficuscr Dec 16 '14 at 21:25
  • I would make object_type, fields link to object_type, objects have an object_type, data links to objects and fields. objects have your unique id – cmorrissey Dec 16 '14 at 21:31
  • also remember to index this properly or you are going to have one slow query when you have a large dataset. – cmorrissey Dec 16 '14 at 21:32
  • @cmorrissey my problem is how to auto assign a unique value for each new record – Jaylen Dec 16 '14 at 21:43
  • @ficuscr I think "auto increment in conjunction with another column" is the correct sentence to describe what I need – Jaylen Dec 16 '14 at 21:45
  • @Mike this may be a good read: http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment composite/compound/two column – ficuscr Dec 16 '14 at 21:49
  • The pattern is called [EAV](http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model), it is the concept behind invention of schema-less databases. In the context you are trying to use, [that will be an anti-pattern.](http://programmers.stackexchange.com/questions/93124/eav-is-it-really-bad-in-all-scenarios) – Mohsen Heydari Dec 17 '14 at 08:36
  • First, I would say, DON'T!! STOP!! This is a really bad idea!! Create a database with fields specific to your project. You are reinventing SQL metadata using SQL metadata. What are you gaining? That said, if there is really some good reason to do this, why not just put an autoincrement field in the table? So sure, if the table includes, say, "accounts", "planets", and "products", then all three will share the same set of IDs. 1 might be an account, 2 a planet, 3 a product, 4 a planet, 5 an account, etc. But so what? – Jay Dec 17 '14 at 15:34
  • @Jay thank you. Wat would be a solution for a CRM system that have many different business groups and each group want somethings slightly different or want some more data captured? How would you solve for this? I thought maybe if I added a dynamic field VIA EAV I would be able to give each business the field that they are asking for without having to alter large tables. Please help me understand your reasons? What if I create the accounts table along with the must have fields like account_id, account_name and if someone need to have a field for year_in_business then I can have that in EAV? – Jaylen Dec 17 '14 at 17:50
  • Attribute/Value tables are bad news because: (a) You are reinventing what already exists in SQL metadata. (b) Access to data now requires matching on the content of text fields rather than on field names, creating the possibility of mistakes and mis-spellings resulting in subtle program bugs. (c) They are difficult to manage, especially is users can create their own attribute names. Al may add "serial number" while Betty adds "serial #" and Charlie adds "sequence number" which turns out to really be the same thing, etc. Then somebody tries to search on serial number and they ... – Jay Dec 17 '14 at 19:36
  • ... complain that it missed hundreds of records (because they were added with "serial #") and this program is all screwed up and the programmers are incompetent. If you're going to actually process the data for anything other than type it in and display it back out and maybe search on it, then you're going to have to know what the data is ahead of time, and if you know that, why not just create fields for it? If the scenario is that users want to be able to invent arbitrary tags, and all that will ever be done with them is that they type values in and later they can display the record ... – Jay Dec 17 '14 at 19:40
  • ... and so you display those values, and maybe the program can search on this label equals this value without having to know that the label or value mean, then yes, EAV is appropriate. If that's what you're doing, I withdraw the rant. But even at that, there is very likely data that does not fit that pattern, and so should not be done with EAV but with "real" fields. – Jay Dec 17 '14 at 19:41

0 Answers0