I do not have much experience in table design. My goal is to create a multi-vendor market place website with laravel that meet the requirement below:

  • Support many kinds of products (10 producs) such as (Car, Home, ..., Book) that each kind of product has a different or same set of field, like:
    • Car has title, color, model, ..., price.
    • Home has title, place, #bedroom, ..., price
    • ...
    • Book has title, author, ..., price

I have two options for design our database as following:

  1. Create one table for all product with same fields and one table for each product with different field.
  2. Create table for each product with all fields.

The question is which of two options is better and some help for how to design standard database?

Thanks for sharing your knowledge.


My approach would be to think in terms of objects. Like John Eisbrener mentioned, think of the types of products that would share common attributes, like model number, weight, ISBN, and so on. But also think of the people that would be involved. A vendor table can list the people selling, and a customers table would list the people buying.

As far as a best practice that I try to live by is don't repeat your data. A table recording a sale should not include a customer's delivery address, that should be referenced through the customers table by using a foreign key constraint. But most of all open up a program like Visio and start with the basic information tables you pretty much know you'll need, and build it out. It's much easier to make changes there than altering tables later

|improve this answer|||||

The best approach will always be in the eye of the beholder (e.g. varying from person to person). Honestly, you need to design your database for your application/website. The answer to your question simply lies in the answer to a question you haven't asked: how will your website handle multiple products?

Let's take a look at Amazon, shall we?

For instance, look at this product listing for the book 1984, by George Orwell.

We see there are few things here that are specific to books:

  • ISBN
  • Publisher
  • Book type (e.g. paperback, hardcover, etc.)
  • A section about the author

We also see there are a few things that are common across all products on the site:

  • The price
  • The product description
  • Customer reviews
  • Similar product recommendations

Now let's look at a 5-pack of 40 amp blade fuses. Again, we see product specific information, such as the model number, item weight, brand, etc. and also we see the more common elements to the site, such as price, product description, customer reviews, and similar product recommendations.

I don't work at Amazon, nor have I ever looked at their underlying product database, but I would suspect they likely have a products table that contains the common information we see on the site, and then there's likely a product detail table that has a many-to-one (e.g. many details per product) relationship with the product table, where the product details table contains a record for each detail item such as ISBN, Author, Publisher, Model Number, Manufacturer, Item weight, etc. There are a number of different ways to design a table structure for this site, but the point I'm trying to make here is the site layout is driving the database design more so than the product information itself, and you should be doing the same thing. I doubt that Amazon has a specific table for Fuses and a separate table for Books, but who knows, maybe they do?

Good database design is a thing that eludes most people, so don't be shocked if your design goes through a few iterations until you land on something that does exactly what you want. Also, do some Google searches for database design best practices, there are a ton of them out there and a lot have different ideas of what best practices mean, but there will be some commonality and that's what I'd say you should start with.

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.