0

I'm making an ecommerce site, using MySQL for storing data. Here is my ERD:

Diagram of my database, I can explain if it is not understandable

CREATE TABLE `admins` (
  `id` int(10) UNSIGNED NOT NULL,
  `shop_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` int(11) NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brands` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `brand_models` (
  `id` int(10) UNSIGNED NOT NULL,
  `brand_id` int(10) UNSIGNED DEFAULT NULL,
  `product_type_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `_lft` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `_rgt` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `top` tinyint(4) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `countries` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `markets` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shops_count` int(11) NOT NULL,
  `products_count` int(11) NOT NULL,
  `photo` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `m_options` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `m_option_values` (
  `id` int(10) UNSIGNED NOT NULL,
  `m_option_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `options` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_type_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `option_values` (
  `id` int(10) UNSIGNED NOT NULL,
  `option_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_resets` (
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_type_id` int(10) UNSIGNED DEFAULT NULL,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `market_id` int(10) UNSIGNED DEFAULT NULL,
  `shop_id` int(10) UNSIGNED DEFAULT NULL,
  `country_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` int(11) NOT NULL,
  `discount_price` int(11) NOT NULL,
  `discount` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  `likes` int(11) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_m_option_values` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_type_id` int(10) UNSIGNED DEFAULT NULL,
  `m_option_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_option_values` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(10) UNSIGNED DEFAULT NULL,
  `option_id` int(10) UNSIGNED DEFAULT NULL,
  `option_value_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_types` (
  `id` int(10) UNSIGNED NOT NULL,
  `category_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `product_type_m_options` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_type_id` int(10) UNSIGNED DEFAULT NULL,
  `m_option_id` int(10) UNSIGNED DEFAULT NULL,
  `m_option_value_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `shops` (
  `id` int(10) UNSIGNED NOT NULL,
  `market_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_types_count` int(11) NOT NULL,
  `products_count` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  `photo` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `admins`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `admins_username_unique` (`username`),
  ADD UNIQUE KEY `admins_phone_unique` (`phone`),
  ADD UNIQUE KEY `admins_email_unique` (`email`),
  ADD KEY `admins_shop_id_foreign` (`shop_id`);

ALTER TABLE `brands`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `brand_models`
  ADD PRIMARY KEY (`id`),
  ADD KEY `brand_models_brand_id_foreign` (`brand_id`),
  ADD KEY `brand_models_product_type_id_foreign` (`product_type_id`);

ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `categories__lft__rgt_parent_id_index` (`_lft`,`_rgt`,`parent_id`);

ALTER TABLE `countries`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `markets`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `migrations`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `m_options`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `m_option_values`
  ADD PRIMARY KEY (`id`),
  ADD KEY `m_option_values_m_option_id_foreign` (`m_option_id`);

ALTER TABLE `options`
  ADD PRIMARY KEY (`id`),
  ADD KEY `options_product_type_id_foreign` (`product_type_id`);

ALTER TABLE `option_values`
  ADD PRIMARY KEY (`id`),
  ADD KEY `option_values_option_id_foreign` (`option_id`);

ALTER TABLE `password_resets`
  ADD KEY `password_resets_email_index` (`email`);

ALTER TABLE `products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `products_product_type_id_foreign` (`product_type_id`),
  ADD KEY `products_category_id_foreign` (`category_id`),
  ADD KEY `products_market_id_foreign` (`market_id`),
  ADD KEY `products_shop_id_foreign` (`shop_id`),
  ADD KEY `products_country_id_foreign` (`country_id`);

ALTER TABLE `product_m_option_values`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_m_option_values_product_type_id_foreign` (`product_type_id`),
  ADD KEY `product_m_option_values_m_option_id_foreign` (`m_option_id`);

ALTER TABLE `product_option_values`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_option_values_product_id_foreign` (`product_id`),
  ADD KEY `product_option_values_option_id_foreign` (`option_id`),
  ADD KEY `product_option_values_option_value_id_foreign` (`option_value_id`);

ALTER TABLE `product_types`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_types_category_id_foreign` (`category_id`);

ALTER TABLE `product_type_m_options`
  ADD PRIMARY KEY (`id`),
  ADD KEY `product_type_m_options_product_type_id_foreign` (`product_type_id`),
  ADD KEY `product_type_m_options_m_option_id_foreign` (`m_option_id`),
  ADD KEY `product_type_m_options_m_option_value_id_foreign` (`m_option_value_id`);

ALTER TABLE `shops`
  ADD PRIMARY KEY (`id`),
  ADD KEY `shops_market_id_foreign` (`market_id`);

I have previously used only LIKE based searches, but in ecommerce I'll have to use fulltext search. Searching must be from name column of every table where name column is exists.

For now I want to index my database only in MySQL but not sure if I can and if it is even possible with this kind of structure.

Later on, when I'll have enough time to learn Elasticsearch I'm planning to use that for indexing.

I want to know your opinions about my database, and get your advice for my search.

  • Is it the best DB structure or should I change something?
  • Will it be good for searching?
  • Is this DB structure indexable in MySQL?
  • Is this DB structure indexable in Elasticsearch?

Edit:

I don't want orders and everything related with it. Because I just can't handle it and I don't think it will be useful in our city, for now. My site will be just like catalog, where you can see which product in which shop at which price. Then if you want you can go and buy it from there. But I'm planning to add "Order" later.

  • I hope my diagram is understandable, if not i can explain – SDeaV Dec 19 '18 at 11:37
0

Its pretty tough to evaluate something like this, but there are a couple of things that are obviously missing

  1. Price might be different in different shops
  2. Price is likely to change over time
  3. Offers are a big thing and have complex rules
  4. Sales are a thing
  5. Stock and warehouse management
  6. Orders, Customers, Baskets, Payments, Refunds?
  7. Products are going to be grouped in odd ways. eg clothes come in sizes and colour ways, cement is ordered by weight etc. Will your options be able to cope?

In terms of your indexing and search ability you will want to use more than one database

Use a relational DB for the relational and transactional data.

Use a NoSql db for things like search results, related products etc

Update the nosql data from the relational database every day

This way you can spread the load of people searching for "rocket ships" and tailor their results, while keeping your underlying data consistent

  • Thank you Ewan for your response. We don't want orders and everything related with it. Because we just can't handle it sad I don't think it will be useful in our city, for now. Our site will be just like catalog', where you can see which product in which shop and you can go and buy it from there. if you want so. But we are planning to add that later. – SDeaV Dec 19 '18 at 17:13
  • And about search. Can I just use only MySQL with its indexing for now?! – SDeaV Dec 19 '18 at 17:16
  • maybe add all that info to your question? – Ewan Dec 19 '18 at 17:16
  • Ok, I'll edit it – SDeaV Dec 19 '18 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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