Example 3: App - The DataJoint Book

You are designing a smart phone app

Users can subscribe for a free account identified by their US phone number without extensions. Provide first and last name, date of birth (optional), and sex (optional).
Users can add one or more credits cards to their account. Store zipcode, expiration date, and the CVV.
The app has paid add-ons called “Track & Field”, “Marathon”, and “Sprint”, each with a fixed price.
A user can purchase each add-on, in which case she must provide a credit card for the purchase. Include a purchase date. A user cannot purchase the same addon twice.

Design¶

import datajoint as dj
schema = dj.Schema('app')

[2024-09-09 19:31:05,706][INFO]: Connecting root@localhost:3306
[2024-09-09 19:31:05,725][INFO]: Connected root@localhost:3306

@schema
class Account(dj.Manual):
    definition = """
    phone : bigint unsigned 
    ---
    first_name : varchar(30)
    last_name : varchar(30)
    dob=null : date
    sex='' : enum('F', 'M', '')
    """

@schema
class CreditCard(dj.Manual):
    definition = """
    card_number :  bigint unsigned 
    ---
    exp_date : date 
    cvv      : smallint unsigned
    zipcode  : int unsigned       
    -> Account
    """

@schema
class AddOn(dj.Lookup):
    definition = """
    addon_id : int
    ---
    addon_name : varchar(30)
    price : decimal(5, 2) unsigned
    """
    contents = ((1, "Track & Field", 13.99), (2, "Marathon", 26.2), (3, "Sprint", 100.00))

AddOn()

@schema
class Purchase(dj.Manual):
    definition = """
    -> Account
    -> AddOn
    ---
    -> CreditCard
    purchase_date : date 
    """

Purchase()

dj.Diagram(schema)

Populate¶

Now we will populate the database with fake data

import random
from tqdm import tqdm
from faker import Faker
fake = Faker()

# insert one account
Account.insert1(dict(
    phone=fake.random_int(1_000_000_0000, 9_999_999_9999),
    first_name=fake.first_name_male(),
    last_name=fake.last_name(),
    sex="M",
    dob=fake.date_of_birth()))

# insert many male accounts
Account.insert(dict(
    phone=fake.random_int(1_000_000_0000, 9_999_999_9999),
    first_name=fake.first_name_male(),
    last_name=fake.last_name(),
    sex='M',
    dob=fake.date_of_birth()) for _ in range(5000))

# insert many female accounts
Account.insert(dict(
    phone=fake.random_int(1_000_000_0000, 9_999_999_9999),
    first_name=fake.first_name_female(),
    last_name=fake.last_name(),
    sex='F',
    dob=fake.date_of_birth()) for _ in range(5000))

# insert some accounts with no sex and no birthdate
Account.insert(dict(
    phone=fake.random_int(1_000_000_0000, 9_999_999_9999),
    first_name=fake.first_name(),
    last_name=fake.last_name()) for _ in range(500))

Account()

# get account ids:
keys = Account.fetch("KEY")

# insert one credit card
CreditCard.insert1(
    dict(random.choice(keys), 
         zipcode=random.randint(10000,99999), 
         card_number=int(fake.credit_card_number()),
         cvv=random.randint(1, 999), 
         exp_date=fake.future_date()))

# insert many credit cards
CreditCard.insert(
    dict(random.choice(keys), 
         zipcode=random.randint(10000,99999), 
         card_number=int(fake.credit_card_number()),
         cvv=random.randint(1, 999), 
         exp_date=fake.future_date()) for _ in range(15000))

# get all possible valid purchases, eliminate duplicate purchases that are under different cards
purchases = (Account * CreditCard * AddOn - Purchase.proj()).fetch("KEY", order_by=('phone', 'addon_id'))
unique_purchases = [purchases.pop()]
for purchase in purchases:
    if (purchase['phone'], purchase['addon_id']) != (unique_purchases[-1]['phone'], unique_purchases[-1]['addon_id']):
        unique_purchases.append(dict(purchase, purchase_date=fake.past_date()))

# insert a random subset 
Purchase.insert(random.sample(unique_purchases, 5000))