0

Suppose I have a website, and I want to add "Search a part" button, and there are two options "Dirt" or "ATV-UTV", and if for example the user choose "Dirt" to he need to choose "Beta" or "Gas" or "Honda", and if he choose "ATV-UTV" he should choose "Suzuki" or "mazda"

                                    [Search a part]
                                    /              \
                                   /                \
                                [Dirt]             [ATV-UTV]
                                /  |  \             /      \
                               /   |   \         [Suzuki]   [mazda]
                            [Beta][Gas] [Honda]

Lest's suppose that the user chose Search a part->Dirt->honda then I want to display the user all the available parts for this "Dirt Honda"

      -------------------------------------------
      |Part No| Price| Stock status| Description|
      -------------------------------------------
      | 56    | 25$  | In stock    | Repair Kit |
      -------------------------------------------
      | 21    |76$   | In stock    | Bearing Kit|
      -------------------------------------------
      | 99    | 54$  | Not In Stock| Repair Kit |
      -------------------------------------------

What's the best way for designing the DB for my website? I mean, how many tables should I build? Should I do 5 tables? on for "Beta" one for "Gas", one for "honda" and also for "Suzuki" and "mazda"?

Just need help on how should I design it, thank you for help

  • Edit your title to be specific about your particular question. – Basil Bourque Oct 22 '17 at 5:59
2

Personally I would create between 3 tables. Of corse you could also put the descriptions in another own table but if the parts table doesn´t get really big in future I personally don´t see the benefit...

table 1: the parts table 2: the manufacturer names table 3: the category (Dirt or ATV-UTV)

  ---------------------------------------------------------------------
  |PartNo | Price| StockStatus | Description|manufacturerId|categoryId|
  ---------------------------------------------------------------------
  | 56    | 25$  | In stock    | Repair Kit |1             |1         |
  ---------------------------------------------------------------------
  | 21    |76$   | In stock    | Bearing Kit|3             |1         |
  ---------------------------------------------------------------------
  | 99    | 54$  | Not In Stock| Repair Kit |1             |2         |
  ---------------------------------------------------------------------

manufacturer table

      ----------------
      |id(PK) | name | 
      ----------------
      | 1     | mazda|
      ----------------
      | 2     | honda| 
      ----------------
.
.
.

category table

  ------------------
  |id(PK) | name   | 
  ------------------
  | 1     | ATV-UTV|
  ------------------
  | 2     | Dirt   | 
  ------------------

. . .

And then you can query them using JOINS

SELECT p.*, m.name AS Manufacturer, c.name AS category FROM parts p
LEFT JOIN manufacturer m ON m.id=p.manufacturerId
LEFT JOIN category c ON c.id=p.categoryID
  • I'd put the StockStatus in a separate table: StockStatus and assign the various statuses with a number. – hot2use Oct 20 '17 at 9:21
  • Yeah as I said, you could normalize everything but the stockStatus I would save as a numeric / bool value anyway... – teldri Oct 20 '17 at 9:29
  • Just what I was thinking. Put an in_stock and ordered column in the parts table and deduce the status from those columns. – hot2use Oct 20 '17 at 9:31

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.