Skip to content

Instantly share code, notes, and snippets.

@koriym

koriym/e-commerce.md

Last active July 15, 2024 06:11
Show Gist options
  • Save koriym/2fe25e74636eb4330844d21a06d9b784 to your computer and use it in GitHub Desktop.
Save koriym/2fe25e74636eb4330844d21a06d9b784 to your computer and use it in GitHub Desktop.
Download ZIP
json-schema-connected Converter example
Raw
e-commerce.md

json-schema-connected Converter example

JSON Schema

{
  "$comment": "This is a medium-complexity social media platform schema set, including user, post, and comment structures with cross-references across multiple files.",
  "$id": "user.json",
  "type": "object",
  "properties": {
    "userId": {
      "type": "string",
      "format": "uuid",
      "description": "The unique identifier for a user."
    },
    "username": {
      "type": "string",
      "description": "The user's display name.",
      "minLength": 3,
      "maxLength": 30
    },
    "email": {
      "type": "string",
      "format": "email",
      "description": "The user's email address."
    },
    "bio": {
      "type": "string",
      "description": "User's short biography.",
      "maxLength": 160
    },
    "followers": {
      "type": "array",
      "items": {
        "$ref": "user.json"
      },
      "description": "List of users following this user."
    },
    "posts": {
      "type": "array",
      "items": {
        "$ref": "post.json"
      },
      "description": "List of user's posts."
    }
  },
  "required": ["userId", "username", "email"]
}
{
  "$id": "post.json",
  "type": "object",
  "properties": {
    "postId": {
      "type": "string",
      "format": "uuid",
      "description": "The unique identifier for a post."
    },
    "author": {
      "$ref": "user.json",
      "description": "The user who created the post."
    },
    "content": {
      "type": "string",
      "description": "The main content of the post.",
      "maxLength": 280
    },
    "timestamp": {
      "type": "string",
      "format": "date-time",
      "description": "The date and time when the post was created."
    },
    "likes": {
      "type": "array",
      "items": {
        "$ref": "user.json"
      },
      "description": "List of users who liked the post."
    },
    "comments": {
      "type": "array",
      "items": {
        "$ref": "comment.json"
      },
      "description": "List of comments on the post."
    }
  },
  "required": ["postId", "author", "content", "timestamp"]
}
{
  "$id": "comment.json",
  "type": "object",
  "properties": {
    "commentId": {
      "type": "string",
      "format": "uuid",
      "description": "The unique identifier for a comment."
    },
    "author": {
      "$ref": "user.json",
      "description": "The user who wrote the comment."
    },
    "content": {
      "type": "string",
      "description": "The content of the comment.",
      "maxLength": 140
    },
    "timestamp": {
      "type": "string",
      "format": "date-time",
      "description": "The date and time when the comment was created."
    }
  },
  "required": ["commentId", "author", "content", "timestamp"]
}

user

Property Type Description Required Constraints
userId string ユーザーの一意の識別子 Yes format: uuid
username string ユーザーのユーザー名 Yes minLength: 3, maxLength: 20
email string ユーザーのメールアドレス Yes format: email
fullName string ユーザーのフルネーム Yes
profileImage string ユーザーのプロファイル画像URL No format: uri
socialLinks object ユーザーのソーシャルメディアリンク No
role string システムにおけるユーザーの役割 Yes
addresses array ユーザーの住所 No
paymentMethods array ユーザーの支払い方法 No
orderHistory array ユーザーの注文履歴 No
wishlist array ユーザーのウィッシュリスト No
cart array ユーザーのショッピングカート No
reviews array ユーザーのレビュー履歴 No
loyaltyPoints integer ユーザーのロイヤルティポイント No min: 0
notifications array ユーザーへの通知 No

address

Property Type Description Required Constraints
addressId string 住所の一意の識別子 Yes format: uuid
street string 通り名と番号 Yes minLength: 1, maxLength: 100
city string 都市名 Yes minLength: 1, maxLength: 50
state string 州または県名 Yes minLength: 1, maxLength: 50
postalCode string 郵便番号 Yes pattern: ^[0-9]{5}(-[0-9]{4})?$
country string 国名 Yes minLength: 1, maxLength: 50

product

Property Type Description Required Constraints
productId string 製品の一意の識別子 Yes format: uuid
name string 製品名 Yes minLength: 1, maxLength: 100
description string 製品の説明 No
price object Embedded: money Yes
category string 製品のカテゴリー Yes
inventory integer 在庫数 Yes min: 0
supplier object Embedded: supplier No
ratings array この製品の評価 No
relatedProducts array 関連する製品 No
images array 製品の画像 No
attributes object 製品の追加属性 No
variations array 製品のバリエーション（例：サイズ、色） No

order

Property Type Description Required Constraints
orderId string 注文の一意の識別子 Yes format: uuid
user object Embedded: user Yes
items array 注文内の項目 Yes
totalAmount object Embedded: money Yes
shippingAddress object Embedded: address Yes
billingAddress object Embedded: address Yes
status string 注文の現在のステータス Yes
orderDate string 注文が行われた日時 Yes format: date-time
trackingNumber string 配送の追跡番号 No
paymentMethod object Embedded: paymentMethod No
discounts array 適用された割引 No
notes string 注文に関するメモ No
coupons array 注文に適用されたクーポン No

money

Property Type Description Required Constraints
amount number 金額 Yes min: 0
currency string 金額の通貨 Yes

rating

Property Type Description Required Constraints
user object Embedded: user Yes
score integer 評価スコア（1-5） Yes min: 1, max: 5
comment string 評価に関連するコメント No
date string 評価が提出された日時 Yes format: date-time

orderItem

Property Type Description Required Constraints
product object Embedded: product Yes
quantity integer 注文された製品の数量 Yes min: 1
price object Embedded: money Yes

supplier

Property Type Description Required Constraints
supplierId string 供給者の一意の識別子 Yes format: uuid
name string 供給者の名前 Yes minLength: 1, maxLength: 100
contact object Yes

paymentMethod

Property Type Description Required Constraints
paymentMethodId string 支払い方法の一意の識別子 Yes format: uuid
type string 支払い方法の種類 Yes
details object Yes

discount

Property Type Description Required Constraints
discountId string 割引の一意の識別子 Yes format: uuid
description string 割引の説明 Yes
amount object Embedded: money Yes
type string 割引の種類 Yes
validFrom string 割引の有効開始日 Yes format: date-time
validTo string 割引の有効終了日 Yes format: date-time

coupon

Property Type Description Required Constraints
couponId string クーポンの一意の識別子 Yes format: uuid
code string クーポンコード Yes
description string クーポンの説明 No
discount object Embedded: discount Yes
validFrom string クーポンの有効開始日 Yes format: date-time
validTo string クーポンの有効終了日 Yes format: date-time

image

Property Type Description Required Constraints
imageId string 画像の一意の識別子 Yes format: uuid
url string 画像のURL Yes format: uri
altText string 画像の代替テキスト No

cartItem

Property Type Description Required Constraints
product object Embedded: product Yes
quantity integer カート内の製品の数量 Yes min: 1
price object Embedded: money Yes

review

Property Type Description Required Constraints
reviewId string レビューの一意の識別子 Yes format: uuid
user object Embedded: user Yes
product object Embedded: product Yes
rating object Embedded: rating Yes
comment string レビューのコメント No
reviewDate string レビューの投稿日 Yes format: date-time

notification

Property Type Description Required Constraints
notificationId string 通知の一意の識別子 Yes format: uuid
type string 通知の種類 Yes
message string 通知のメッセージ Yes
read boolean 通知が既読かどうか No
date string 通知の日時 Yes format: date-time

productVariation

Property Type Description Required Constraints
variationId string バリエーションの一意の識別子 Yes format: uuid
name string バリエーション名（例：サイズ、色） Yes
value string バリエーションの値 Yes
additionalPrice object Embedded: money No 
CREATE TABLE user (
  user_id STRING NOT NULL,
  username STRING NOT NULL,
  email STRING NOT NULL,
  full_name STRING NOT NULL,
  profile_image STRING,
  social_links JSON,
  role STRING NOT NULL,
  addresses JSON,
  payment_methods JSON,
  order_history JSON,
  wishlist JSON,
  cart JSON,
  reviews JSON,
  loyalty_points INT,
  notifications JSON
);

CREATE TABLE address (
  address_id STRING NOT NULL,
  street STRING NOT NULL,
  city STRING NOT NULL,
  state STRING NOT NULL,
  postal_code STRING NOT NULL,
  country STRING NOT NULL
);

CREATE TABLE product (
  product_id STRING NOT NULL,
  name STRING NOT NULL,
  description STRING,
  price_id INT NOT NULL,
  category STRING NOT NULL,
  inventory INT NOT NULL,
  supplier_id INT,
  ratings JSON,
  related_products JSON,
  images JSON,
  attributes JSON,
  variations JSON,
  FOREIGN KEY (price_id) REFERENCES money(id),
  FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
);

CREATE TABLE order (
  order_id STRING NOT NULL,
  user_id INT NOT NULL,
  items JSON NOT NULL,
  total_amount_id INT NOT NULL,
  shipping_address_id INT NOT NULL,
  billing_address_id INT NOT NULL,
  status STRING NOT NULL,
  order_date STRING NOT NULL,
  tracking_number STRING,
  payment_method_id INT,
  discounts JSON,
  notes STRING,
  coupons JSON,
  FOREIGN KEY (user_id) REFERENCES user(user_id),
  FOREIGN KEY (total_amount_id) REFERENCES money(id),
  FOREIGN KEY (shipping_address_id) REFERENCES address(address_id),
  FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
  FOREIGN KEY (payment_method_id) REFERENCES paymentMethod(payment_method_id)
);

CREATE TABLE money (
  amount DECIMAL NOT NULL,
  currency STRING NOT NULL
);

CREATE TABLE rating (
  user_id INT NOT NULL,
  score INT NOT NULL,
  comment STRING,
  date STRING NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(user_id)
);

CREATE TABLE orderItem (
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  FOREIGN KEY (price_id) REFERENCES money(id)
);

CREATE TABLE supplier (
  supplier_id STRING NOT NULL,
  name STRING NOT NULL,
  contact JSON NOT NULL
);

CREATE TABLE paymentMethod (
  payment_method_id STRING NOT NULL,
  type STRING NOT NULL,
  details JSON NOT NULL
);

CREATE TABLE discount (
  discount_id STRING NOT NULL,
  description STRING NOT NULL,
  amount_id INT NOT NULL,
  type STRING NOT NULL,
  valid_from STRING NOT NULL,
  valid_to STRING NOT NULL,
  FOREIGN KEY (amount_id) REFERENCES money(id)
);

CREATE TABLE coupon (
  coupon_id STRING NOT NULL,
  code STRING NOT NULL,
  description STRING,
  discount_id INT NOT NULL,
  valid_from STRING NOT NULL,
  valid_to STRING NOT NULL,
  FOREIGN KEY (discount_id) REFERENCES discount(discount_id)
);

CREATE TABLE image (
  image_id STRING NOT NULL,
  url STRING NOT NULL,
  alt_text STRING
);

CREATE TABLE cartItem (
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  FOREIGN KEY (price_id) REFERENCES money(id)
);

CREATE TABLE review (
  review_id STRING NOT NULL,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  rating_id INT NOT NULL,
  comment STRING,
  review_date STRING NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(user_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  FOREIGN KEY (rating_id) REFERENCES rating(id)
);

CREATE TABLE notification (
  notification_id STRING NOT NULL,
  type STRING NOT NULL,
  message STRING NOT NULL,
  read BOOLEAN,
  date STRING NOT NULL
);

CREATE TABLE productVariation (
  variation_id STRING NOT NULL,
  name STRING NOT NULL,
  value STRING NOT NULL,
  additional_price_id INT,
  FOREIGN KEY (additional_price_id) REFERENCES money(id)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment