[Discussion] - Table Designs

[NISH1001]

I am thinking of having 2 tables on the application:

• table 1 to store catalog metadata (UUID, filename, checksum, etc)
• table 2 to store transfer information from source/destination (status, etc)

We could use one universal identifier for each file (UUID), consistent over both source and destination. The application will be CRUD on these two tables. Table 1 could be updated occasionally (by uploading csv file?), table 2 could be done frequently.

The models are tentatively as:

class CatalogueItem(db.Model):
    __tablename__ = f"{TABLE_PREFIX}catalogueitem"
    uuid = db.Column(db.String, primary_key=True)
    name = db.Column(db.String)
    content_length = db.Column(db.BIGINT)
    ingestion_date = db.Column(db.DateTime)
    content_date_start = db.Column(db.DateTime)
    content_date_end = db.Column(db.DateTime)
    checksum_algorithm = db.Column(db.String)
    checksum_value = db.Column(db.String)

class TransferInfo(db.Model):
    __tablename__ = f"{TABLE_PREFIX}transfer_info"
    transfer_id = db.Column(db.String, primary_key=True)
    catalogue_uuid = db.Column(db.String, db.ForeignKey("catalogue_item.uuid"))
    catalogue_item = db.relationship(
        "CatalogueItem", backref=db.backref("catalogue_item")
    )
    status = db.Column(db.String)  # IN-PROGRESS/COMPLETED/TODO
    checksum_verification = db.Column(db.String(10))  # GOOD/BAD
    started_on = db.Column(db.DateTime)
    completed_on = db.Column(db.DateTime)
    source = db.Column(db.String, nullable=True)  # ESA/NASA?
    destination = db.Column(db.String, nullable=True)  # ESA/NASA

@smarru @freitagb

[NISH1001]

As of now, I have decided to go with single-table mode.