0

I am new to the world of DB design and was hoping to get some pointers on the design I have been thinking of using. My goal is to track applications that are publicly listed on a website. Every hour, I will get get these applications from the website.

Any application that is listed on the website is an active application. When they become inactive, they get removed from the website. My intent is to scrape the website and compare the applications in the database with those that I just got from the website. Those that are in the scrape, but not the DB will be inserted. Those that are in the DB but not in the scrape, will be marked as "inactive" in the DB.

One more thing I want to do is keep track of application history. I intend on doing this by comparing applications from the scrape to those in the DB. If their titles match, but any of the fields differ, then the application will be updated. The old version will be stored in an application_history table. Even if the application gets archived, I do not want to lose the history.

To make this work I was planning on using 4 tables. There will be a master applications table that contains all of the active and inactive applications. The applications do not have a unique ID, so I planned on hashing the title to use it as an ID:

CREATE TABLE applications (
    id VARCHAR(64) PRIMARY KEY,
    url VARCHAR(255),
    title TEXT,
    summary TEXT,
    proposal TEXT,
    -- more fields...
);

The active_applications table will just contain the currently active applications (those that come from the scrape):

CREATE TABLE active_applications (
    id VARCHAR(64),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id) REFERENCES applications(id)
);

The archived_applications table would look something like this.:

CREATE TABLE archived_applications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id VARCHAR(64) NOT NULL,
    archived_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES applications(id)
);

Lastly, there would be the application_history table that keeps track of changes in the applications. This one I am not too sure about. Any ideas on the best way to keep track of changes?

CREATE TABLE application_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id VARCHAR(64) NOT NULL,
    changed_data TEXT,
    change_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES applications(id)
);

Overall I am looking for feedback on if this would be logical. Is this structure conducive to moving values from active to archive?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.