Database design for a product with lots of fields

I usually try to thing like this. I have lots of books in my table, every book has an author but an author has many books.

To reduce repetition I extract the authors to their own table. The same goes for the genre since a books has a genre and a genre has many books. Then I continue through each of the fields I need and extract them if needed.

As for images I'd probably use a pivot table to join my images table to my product table.

I have one term for you that can fix this all for you! It's called polymorphic relationships! This is a way on how you can connect multiple items of the same type to one other model.

You can find more here in the documentation: https://laravel.com/docs/5.6/eloquent-relationships#polymorphic-relations

Let me know if this works for you ;)

@MrChrxs to be flexible you really have to break a product down into multiple tabels.

product table with sku, qty, manufacturer_id, is_active

product_groups (which group a product belongs to, eg clothes, software, furniture...)

product_texts table with title, slug (seo), text

product_resources with image(fields), mimetype

product_prices with retail price, wholesale price, special prices tax, from-to date for special action

product_options (eg black, red, green, square...)

product_attributes (eg small, medium, large, 1mb, 2mb, 3mb...)

and you need the appropriate lookup tables as well

as you can see, it's pretty complex but it's worth to think about the structer upfront (as you do) since this time spent is well worth it. As some help you can take a look at the db structure of some bigger ecommerce programs for some input/ideas.

Thank you, @bobbybouwmann, I'll take a look at see if it helps.

@frezno, thank you! I was thinking of doing it more that way, but I was trying to condense the amount of fields in a table, and the amount of tables, if possible. I've updated my original post with an image of the types of fields I would be using/

why condensing fields and/or reducing the amount of tables, @MrChrxs ?

Yound don't get a reward by using the tiniest way of handling it. On the other hand, you know best, what products you are handling with.

But think further ahead. With the structure right now you are limited in the future. Could it be, that you have prices will change, or on a sale you might want to display the old price. There's no (additional) field for such a special. Could it be that you have the very same seat, made of crocodile and rhino leather and in colors nature, red and green? How do you want to handle that?

Restructuring the dabase later due to new, upcomming requirements is a way bigger pita than thinking a little ahead and trying to prepared to future changes/additions.

But, as said, it's just you who knows your products and know what might be comming up.

Agree with @frezno but he's also right that it really depends on the business rules. Do you need to track pricing? Would you ever need to know what the price of something was a year ago? Will you have invoices, that will refer to prices? Will you need to be able to go back and generate a sales report for last year? What if the price changed 4 times during that year? It might be (slightly) more complex to create these additional tables, but you will kick yourself if you don't do it right from the get-go and allow for future (maybe current) scenarios that you haven't/aren't thinking about.

So before coding this stuff, have a firm grasp on all of the business rules that it needs to follow and anticipate reasonable changes in the future based on the current rules. Know how you need to use the data. That should dictate the design.

For images I would recommend Spatie media manager. it will look after all the image variants and relate them back to the single product model

For specifications, a commpn pattern is to have multiple rows per product, with attribute and value columns, possibly also a sequence column

eg

• id
• product_id
• attribute
• value
• sequence

and then your 18 specifications are in 18 rows for the product and not a fixed 18 columns

This also helps when some items only have 6 specifications and others suddenly need 22

Thank you @frezno I will definitely try to work out what I might need in the future, and try and break it down into relevant tables.

@Snapey I'll take a look at the Spatie manager. I thought about the multiple rows, and was initially going to go with that route, but wanted to see if it was good practice from experienced developers perspective.

Its called an EAV model

https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

There are of course pros and cons to using it - it depends on how stable your 'specifications' are and if you need to add new ones without having to rebuild your application.