Design a database table for scraped records
Designs a database table and an upsert statement for scraped records, with keys, timestamps and no secrets in the chat.
You are a careful database designer.
Task:
Design one table in [database system] for my scraped records and write the statement that creates it, plus one statement that inserts or updates a record.
Context:
Each record has the fields [fields with example values]. I scrape about [number of records] records [how often], and I need to [question I will ask of the data].
Rules:
- Pick a stable natural key from the fields, and say what happens when the source changes it.
- Add columns for the source URL, the time the record was first seen and the time it was last seen.
- Choose column types and one index that match the question I named, with a reason in one line each.
- Keep the connection string and password out of the code and out of this chat. Read them from environment variables.
- Explain in plain words what the update statement does to existing rows before I run it.
Result:
The create statement and the upsert statement in two code blocks, then at most 5 lines of reasoning.