SQL Schema for events

[ats1999]

Sample Data

Every event will have it's own schema

// event = page_view
{
   "url":"https://dsabyte.com/blog/dsabyte/news/dsabyte-Open-Graph-Image-Generator-26384f92-83da-4da6-92fd-a8e5ccd73dc1",
   "domain":"dsabyte.com",
   "path":"/blog/dsabyte/news/dsabyte-Open-Graph-Image-Generator-26384f92-83da-4da6-92fd-a8e5ccd73dc1",
   "country":"IN",
   "device":"DESKTOP",
   "ip":"127.0.0.1",
   "browser":"Chrome",
   "os":"linux",
   "ts":"1706380834215"
}

// event = sales
{
  "event": "purchase",
  "timestamp": "2024-02-04T15:30:00",
  "user_id": "12345",
  "product_id": "6789",
  "quantity": 2,
  "total_price": 50.00,
  "payment_method": "credit_card"
  "customer_age":29
}

[ats1999]

There are two possible solutions that comes to the mind are

1. Create new table for each event
2. Store event data as a JSON attribute into SQL database

Questions

• What are the disadvantages of creating tables dynamically, without any limit
• What are the disadvantages of having JSON column

[ats1999]

TBD

• Dimensional Model
• Star Schema
• Map Reduce (Spark)

[ats1999]

• https://www.uber.com/en-IN/blog/logging/
• https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/
• https://www.youtube.com/watch?v=cMdQsxolcqc
• https://clickhouse.com/use-cases