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

1
  • Edit your title to be specific about your particular question. Commented Oct 22, 2017 at 5:59

1 Answer 1

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
3
  • I'd put the StockStatus in a separate table: StockStatus and assign the various statuses with a number.
    – John K. N.
    Commented Oct 20, 2017 at 9:21
  • Yeah as I said, you could normalize everything but the stockStatus I would save as a numeric / bool value anyway...
    – teldri
    Commented Oct 20, 2017 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.
    – John K. N.
    Commented Oct 20, 2017 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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