1

I'm trying to design schema for my personal project that would be an online gaming shop. I would like to ask you guys for any suggestions/tips on how could I maybe improve my current draft.

Concerns:

My biggest concern are the Variants. I'm not sure what are the consequences of defining various prices for the same product. I'm also not sure about optional relation between Variants and Servers, main purpose of it is to provide an option for admin to sell products that will be available on specified Servers of the Game f.e Game Currency.

Info:

  • Game

    1. Attributes:
      • name
      • description
    2. Associations:
      • many ServerTypes
      • many Categories
  • Category

    1. Attributes:
      • name
      • description
      • game_id FK
    2. Associations:
      • many Products
  • Product

    1. Attributes:
      • name
      • status
      • description
      • category_id FK
    2. Associations:
      • many Variants
  • Variant

    1. Attributes:
      • name
      • stock_quantity
      • price
      • server_id FK NULLABLE
      • product_id FK
    2. Associations:
      • many OrderItems
  • Order_Item

    1. Attributes:
      • price
      • quantity
      • variant_id FK
      • order_id FK
  • Order

    1. Attributes:
      • price
      • status
    2. Associations:
      • many Order_Items
      • many Payments
  • Payment

    1. Attributes:
      • status
      • amount
      • order_id FK
  • Server

    1. Attributes:
      • name
      • server_type_id FK
    2. Associations:
      • many Variants
  • Server_Type

    1. Attributes:
      • name
      • game_id FK
    2. Associations:
      • many Servers

Examples of usage:

Admin

  • Admin can create Game record
    • Game can contain any number of ServerTypes (eg. EU/NA)
      • Each ServerType can contain any number of Servers
      • Each Server can hold any number of Variants
    • Game can contain any number of Categories (e.g game codes).
      • Each Category can hold any number of Products (e.g premium voucher).
      • Each Product can hold any number of Variants (e.g 30 days - 5 usd, 90 days of premium voucher - 15 usd).
      • Each Variant can (optionally) belongs to Server
      • Variant can have different price (not sure if it's a good thing)

User

  • User can make an Order
    • it has many Order_Items
    • OrderItem's price = Variant's price * quantity
    • Order's price will be a sum of OrderItem's price
  • User can make a Payment
    • it will belong to specific Order
    • it has an amount to be paid
    • it will change it's status over time

Additional info:

I've also made a quick diagram sketch of my schema:

enter image description here

  • 1
    (A) Table names should be singular, not plural, and not use camelCase. (B) You’ve not described any of your terms: category, product, variant, server, or server type. So we cannot offer insights. (C) Your servertype table should have a foreign key to server table. And Server table has a mysterious foreign key to a non-existent table of worldtype. – Basil Bourque Nov 19 '18 at 6:11
  • 1. Ok I've edited it as you said (it is just a sketch though). 2. Will edit in the post. 3. My mistake, world_type FK meant to be server_type. – mist Nov 19 '18 at 9:50
  • I've edited my post, not sure what I could add more to it. – mist Nov 20 '18 at 11:24

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.