2

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below:

Product Table
  p_id
  p_title
  category_id   F Key

Category_Table
  c_id
  c_name

Sub_category_Table
  sc_id
  sc_name
  c_id      F Key

Now, the problem is, for example, I have 8 categories and about 60 subcategories with some common column names and some quite different like this:

Vehicle                           // a category
  Car                          // subcategory
      id,
      cat_id
      brand_id
      model
      price
      year
      insurance_validity_date
      fuel_type

  Motorcycle                          // subcategory
      id
      cat_id
      brand_id
      model
      price
      year

  Bus/Lorry                          // subcategory
      id
      cat_id
      brand_id
      model
      price
      year
      insurance_validity_date
      fuel_type

Now I'm merging all the same columns in one table named Vehicle and making a new table named car to store the insurance_validity_date, fuel_type columns and no table for Motorcycles at all like this:

Vehicle (= Motorcycle)                           // a category
      id
      cat_id
      brand_id
      model
      price
      year

 Car/Bus                          // subcategory
      id
      insurance_validity_date
      fuel_type
      vehicle_id

It means there is no need to create a Motorcycle table, and Car and Bus will be stored in a single table. Is it right?

  1. Motorcycle is a Vehicle
  2. Car is a Vehicle
  3. Bus is a Vehicle

The vehicle_id value will identify whether the record is a bus or a car.


Here is a similar example for users. I've three types of users: admin, registered and guest with the following schema:

Person
  user
      id
      name
      user_name
      email
      contact_no
      password
      no_of_log_ins
      last_login_time
      registration_date
      city
      user_role_or_type_id

  business_owner
      id
      name
      user_name
      company_business_shop_name
      email
      contact_no
      address
      password
      no_of_log_ins
      last_login_time
      registration_date
      city
      user_role_or_type_id

   guest
      id
      name
      email
      contact_no
      city
      user_role_or_type_id

   admin
      id
      name
      email
      contact_no
      password
      no_of_logins
      last_login_time
      user_role_or_type_id

Now I'm merging all the same columns in one table named Person and modifying user, guest and admin to have type-specific columns like this:

Person (= Guest)
      id
      name
      email
      contact_no
      city
      user_role_or_type_id

   guest
     id
     Person_id

   registered_user (or admin)
      id
      person_id
      user_name
      password
      no_of_logins
      last_login_time

          admin
             id
             registered_user_id

          user                              
             id
             registration_date
             registered_user_id

          business_owner
             company_business_shop_name
             address
             registered_user_id

This model means:

  1. Guest is a Person
  2. Registered user is a Person
    (i) user is a Registered User
    (ii) business_owner is a Registered User

That's all what I think a solution for me. Is it a good solution? Would it not degrade the performance? Should I look for any other solution? I need some guidance.

Your Answer

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

Browse other questions tagged or ask your own question.