Relations

@Aetrox I’d say you have users that have many devices, and a device has many attributes. An attribute would be key–value pairs for things like colour, storage capacity etc. Serial number sounds pretty standard, so I’d be tempted to move that to the Device model.

@martinbean thanks for your reply,

so do i get it right that you would do the tables like this:

user hasMany devices devices hasMany attributes attributes hasOne Color attributes hasOne Manufacturer attributes hasOne Model attributes hasOne Storage

So it would look like this? Color 1 | Silver 2 | Spacegrey Model 1 | iPhone 8 2 | iPhone 7 Attributes 1 | color_id 1| model_id 2 2 | color_id 2| model_id 1 Devices: 1 | | And i have a pivot table: device_attribute: ID device_id atribute_id 1 1 2

Please correct me if i am wrong and thank you very much @martinbean

@Aetrox No. Attributes would be key–value pairs. So attributes would have name and value columns. The name may be colour and the value red.

@martinbean Oh so you mean only 3 tables: User -> Devices -> Attributes

And in Attributes are the columns Storage Color Model etc. ?

Wait, no you mean a column with name and value

so name would be Color, Storage etc and the values would be red, 256 and so on.

I cant imagin how this would look like with many devices. :/ feeling a bit dumb at the moment.

So a device has many attributes like Red, iPhone, 256, Apple

But another device has Blue, S9, 64, Samsung.

Wouldnt that mean i need an attributes table for each device, or do i have multiple Color rows like:

Color:Red, Color:Blue, Color:Pink

I cant wrap my head around this.

@Aetrox A device would have many attributes, so that means your attributes table would have a device_id foreign key:

Schema::create('devices', function (Blueprint $table) {
    $table->increments('id');
    $table->string('serial_number')->unique();
    $table->timestamps();
});

Schema::create('attributes', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('device_id');
    $table->string('name');
    $table->string('value');
    $table->timestamps();
    $table->foreign('device_id')->references('id')->on('devices')->onDelete('cascade');
    
    // Stops a single device having multiple attributes with the same name
    $table->unique(['device_id', 'name']);
});

@martinbean Okay now i understand. So i have for the device with the id 1

id  1 Color             Blue        timestamp
id  1 Storage       64          timestamp
id  1 Manufacturer  Apple       timestamp
id  1 Model             iPhone 8    timestamp

thank you very much

you could further complicate (normalise it) by

    $table->increments('id');
    $table->unsignedInteger('device_id');
    $table->string('name');
    $table->string('value');
    $table->timestamps();```

could be

Schema::create('attributes', function (Blueprint $table) { $table->increments('id'); $table->unsignedInteger('device_id'); $table->string('attribute_id'); $table->string('value_id'); $table->timestamps();

attribute_id = > table with id, name  e.g  1, Colour, 2 Storage, 
value_id => table with all options id, attribute_id, name:  1, 1, Blue,  2, 1, Red,  3, 2, Storage_value

@martinbean @shez1983

Yes i could further normalize it, But in the Lesser complicated way it is hard enough to Format the output later. I Return my Query as json, and Then have to iterate over and over to get the whole data.

I have a User loop over the devices, Loop over attributes, Loop over contracts etc.. i Hope i someday find a better way to get my Output. Thank you very much for your help @martinbean @shez1983