If you have to dynamically change the structure of the DB to accomodate the data, it is a bad DB design.
delmadord said:
If you have to dynamically change the structure of the DB to accomodate the data, it is a bad DB design.
Thanks for your reply, but I think it's not. The data is in a dynamic state and changes over time, so the DB has to reflect that and should be altered accordingly.
An admin (not dev) adds an additional column to a dataset table and sets it as active, so the users in the system can enter additional data to the dataset. If the admin deletes that data item, the system sets it to inactive, so users can't see or alter that item. From time to time there is a cron job to clean out inactive data items.
Is there a known approach to that?
I would like to apologize to my short previous response. You probably know better what are you doing, not me. I would only like to add, that if you really want to do that, then you should also somehow manage to generate a migration file for every DB change, otherwise the migrations have no point, they will not be consistent.
The common solution to problems similar to this one is to do a meta data tables.
I have a basic migration with some basic columns, which is set via a xml file. The additional columns would depend on the needs of the specific installation. So the migration is consistent, but just to the basics.
delmadord said: The common solution to problems similar to this one is to do a meta data tables.
Interesting. Never heard of that. could you give me link or an example?
As far as I know, there is not a way to create database tables using Laravel or its Schema class.
Can you not just use a seperate table, link to the original record with a foreign key and then just use key => value and add rows?
mcblum said:
Can you not just use a seperate table, link to the original record with a foreign key and then just use key => value and add rows?
I considered this, but it would end in a hell of a lot of tables in the DB. What if the user want's to extend that table? And how do I set the columns dynamically based on user settings?
Here is the user interaction for the app, which is about statistical data. The user creates a new statistic (like counting birds) and sets the columns 'female/male', 'amount', 'color'. Then gives the columns names and sets the type. After some statistical data is entered, he wants to add additional columns to the table, like 'size'.
I kind of found a solution, but this is a near endless if-else statement. Serves the purpose right now, but I'm open for better suggestions.
$dataitems = DataItem::all();
Schema::create('data_set', function(Blueprint $table) use ($dataitems)
{
$table->integer('id')->unsigned();
$table->timestamps();
$table->softDelete();
// loop $dataitems and create corresponding column in DataSet
foreach ($dataitems as $item) {
if ($item['type'] == 'string') {
$table->string($item['name']);
} elseif ($item['type'] == 'float') {
if ($item['unsigned'] && $item['nullable']) {
$table->float($item['name'])->unsigned()->nullable();
} elseif ($item['unsigned'] && !$item['nullable']) {
$table->float($item['name'])->unsigned();
} elseif (!$item['unsigned'] && $item['nullable']) {
$table->float($item['name'])->nullable();
} else {
$table->float($item['name']);
}
} elseif ($item['type'] == 'integer') {
if ($item['unsigned'] && $set['nullable']) {
$table->integer($item['name'])->unsigned()->nullable();
} elseif ($item['unsigned'] && !$item['nullable']) {
$table->integer($item['name'])->unsigned();
} elseif (!$item['unsigned'] && $item['nullable']) {
$table->integer($item['name'])->nullable();
} else {
$table->integer($item['name']);
}
}
};
});
Hi dirkolbrich - have you modified your approach regarding dynamic fields? I was in a similar predicament and we couldn't think of another way either. Hope all is well.
Sign in to participate in this thread!
Laravel
Laravel News
Laracasts
Laravel Podcast