Database Architecture: Multiple Type Relations

Question

I'm building a Laravel application that has "listings". These listings can be things like boats, planes, and automobiles; each with their own specific fields.

I will also have an images table that should relate to each type of listing and a users table that needs to map to each type of listing. I'm trying to determine the best way to map each listing type back to images and users.

One way I've thought of doing this was having separate boats, planes and automobiles tables with their specific fields and then having specific boat_images plane_images and automobile_images tables to map to each respective type. But then relating each type to a user would be a bit tricky.

I don't think one giant listing table with all fields I'd ever use through these 3 (which could grow in size later) would make sense --- and I also don't believe having a general metadata field that has a JSON object full of specifications for each listing would work well either when I want to have a searchable database.

I know of pivot tables, but I'm trying to grasp the overall architecture here. Any help would be greatly appreciated. Thanks!

You don't have to separate images table. The images table can be a single table that can store your boats, planes and automobiles images. Laravel can handle it. See laravel.com/docs/5.0/eloquent#polymorphic-relations to see example of code and idea. — MaXi32
– MaXi32, Commented Aug 31, 2015 at 7:48

user2094178 · Accepted Answer · 2015-08-30 23:55:01Z

2

You could have a listings table, holding only id and name. Boats, planes, automobiles and others should be a subset table.

Each table will have its own entity. And the Listing entity will have multiple hasMany relationships with its subset tables. These relationships will be named like boats(), planes(), etc. Each subset listing entity will hold a single belongsTo relationship.

Using these subset tables should also help to compartmentalize form validation.

You can have a single images table and use a polymorphic relationship towards the listings table. This one is a huge savior.

answered Aug 30, 2015 at 23:55

user2094178

9,47410 gold badges45 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zach Over a year ago

Thanks so much for the tips! So as far as the name goes, do you mean the name of the subset table, or just the generic title that could be used to identify the item? In this case, I probably wouldn't have a title, as it would be auto-generated by the vehicle information selected. Thanks again!

user2094178 Over a year ago

I meant the generic title. You can go even further using polymorphic relationships to handle boats, planes, automobiles, etc.

Collectives™ on Stack Overflow

Database Architecture: Multiple Type Relations

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related