search

cityofaustin / atd-data-tech

Austin Transportation Data & Technology Services
17 stars 2 forks source link

Database: Drop `moped_types.type_order` #8790

Open frankhereford opened 2 years ago

frankhereford commented 2 years ago

We are using the moped_types table in differently than the way it was initially designed. In our iteration, many projects share the same type, so columns that store project-specific type information are illogical and should be dropped for clarity.

None of these currently contain any data.

amenity commented 2 years ago

It may be worth considering if we want to keep on_street and sensitivity

Hmm... We should check what these values currently are in Access. I have a feeling we might be using this table in a different way than JD intended.

@frankhereford - here's an old .mdb export of the database. I can ask Nathan for a new one, if you want.

@johnclary - is ☝🏻 how you usually inspect IMPDB data? Assuming you're not using VPN + Citrix like I would have to.

frankhereford commented 2 years ago

@amenity - thank you a ton for the copy of the IMPDB, that is super helpful!

I got in there to find the table which inspired the use of a types table in our database, and I'm very confident that the Project_Types table is our source. Thank you again for enabling me to find more concrete information instead of taking guesses.

Your feeling that we're using the table in a wholly different way is exactly right. We store an integer in our projects that refers to our moped_types table. Each project can get one type and each type can be linked to multiple projects.

The IMPDB does it differently, where each type belongs to only one project and a project can have multiple types.

The way we've flipped it around is totally normal, but it does make a type record a stand-alone thing where in the IMPDB, a type is a child of a project, can not stand alone, and can contain information about specific projects.

Based on the way we've repurposed this table, in my opinion, it no longer makes sense to maintain on_street and sensitivity columns in our type database, as they were initially used to store per-project data.

I hope this is helpful. If I can help explain any of this better, please let me know, and I'll do my best. Thanks! 🙏

amenity commented 2 years ago

@frankhereford - finally getting to reading this thoroughly. 😅 Totally agree with your assessment of the situation — thanks for explaining. I'm editing the description of this issue so we can strike all three at once.

johnclary commented 1 year ago

We are going to completely rethink the concept of project types once we finish implementing work types (which is effectively project types at the component level).

This work is going to be swept up into the migration as we separate component work types from project-level types of things in the Access data model. Examples of the reimagined project types would be "Private Development" or "Capital Improvement Project (CIP)".