Advice on crating database properties

posted 10 years ago

Hi guys,

This question might be more of a generic database design question, but I'm using it for a Laravel project and trying to figure out the most efficient Eloquent solution, so I hope it's ok.

I have a table of objects that needs properties in my database. Each object can have an unlimited amount of properties, and each property can be of a different type, like integer, string, long text, array and such. For example, if the parent object was a User, I want to be able to add properties like "Age" (integer) and "Favorite pizza" (string) for one specific user, while another user has the property "Favorite color". Ie, different properties for each object. What is the best approach for this?

My problem is the different types a property can be. There is no problem adding a Property table like

Property table:

id
parent_id
name
type
int_value
string_value
text_value
...

..where you set each value in the correct colum. For example, "Age" would have title=Age, type=int, int_value=user_age and the rest of the value fields nulled. You can probably override the newFromBuilder() method and even create different models for each type of property depending on the type field, However this doesn't really seems like a great solution as it has some overhead (lots of empty fields on each Property, new type requires adding a column on ALL other properties, and so on).

Instead I'm thinking some kind of multi table inheritance approach, where the property only contains parent, something like this:

Property table:

id
parent_id
name
type


Int_value table

id
property_id
value (type int)


String_value table

id
property_id
value (type varchar)

Ie, every property is of a different type, preferably with a different Model. This feels cleaner, but seems like a headache to select from the database. I would like to be able to do something like

User->where(...)->with('properties.values')->get();

..and have the properties be a collection of different models that can be things like PropertyInteger, PropertyString etc. Or will the overhead from splitting the values over several tables make the queries so slow it's not worth it compared to a bunch of null fields in a single table?

I have also seen some approaches that uses something like an "attributes" column that just contains other json-encoded attributes, but that seems like a nightmare to search.

Does anyone have a suggestion on how to best solve this?

Last updated 3 years ago.

donaldrecord

replied 10 years ago

Have you played around with Laravel's migrations and seeding? That might be your best solution. If you are unsure about you want to design your database, Migrations makes creating and dropping DB objects very easy until you have a design you are pleased with.

URL: This is a link

Here is an example users table. You would still need to refer to the link I posted if you want to go this route.

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration {

	public function up()
    {
        Schema::create('users', function($table)
        {
            $table->increments('id');
            $table->string('email')->unique();
            $table->string('favorite_pizza');
            $table->string('username');
            $table->string('password');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('users');
    }
}
?>

Last updated 3 years ago.

erikeklund

replied 10 years ago

Yes, I'm aware of migrations, but that's not the issue here. It's not that I want to add properties to all objects, I want all objects (users, to continue the example) to have different properties. Ie, user #1 has the property "Age = 5", while user #2 has the property "Middle name = Gary". The properties is different for each object, and there can be anything from 0 to 1000 properties per object (ie, per row in the database, not per type of object).

One real-life example is that users can create custom html forms. I have pre-defined fields they can add to their form (for example, single line text, multiline text, checkbox and so on). Each field has different properties - text fields might have a "placeholder" property, while checkboxes has a "checked" property.. Another field that accept dates might have a "mindate" and "maxdate" property.

The question is more - how do I design such properties in the database, and connect this through Eloquent so that I can do something like

$fields = Field::whereForm($myFormId)->with("properties.values")->get();

And get the linked properties. The properties would be a collection of property objects, containing something like:

echo $myField->properties[0]->name; // output: "placeholder"
echo $myField->properties[0]->value; // output: "Type Here" (string)
echo $myField->properties[1]->name; // output: "max_length"
echo $myField->properties[1]->value; // output: 30 (int)

Last updated 3 years ago.

Moderators