MySQL database Development

[GMaynard1]

Suggestion Category

Data Storage

Suggestion Description

ERD is here

• Green tables have scripts below

• Red tables need to be drawn up and scripted

• White tables need to be scripted

• [x] Contacts table to store info about eMOLT participants and support staff

• [x] Ports table based on FVTR.Ports, standardized with other NEFSC databases but including some eMOLT specific fields

• [x] Programs table to track which type of eMOLT the vessel participates in

• [x] Funding table to track how vessel equipment is funded

• [x] Vessel table to track contact information, gear types, program affiliation, home port, etc.

• [x] [Equipment inventory to track what eMOLT currently owns and maintains]()

• [x] Vessel Program table to allow one vessel to participate in multiple programs

• [x] Vessel Visit Log table

• [x] Equipment Change table to track probe and comm deployments

• [x] Hardware address data to store MAC, Satellite, Bluetooth, etc. addresses

• [x] Gear code table similar to FVTR.FVTR_GEAR_CODES

Raw data storage tasks TBD

[GMaynard1]

@Friskfisk we should talk about raw sensor data storage since I know you guys are working on a MySQL database and there's an existing (clunky) Oracle architecture at the NEFSC for Study Fleet participants. There's also a set of Oracle tables for @jamespatrickmanning 's traditional eMOLT vessels (non-realtime). I'd like to fold all of those efforts into one place if possible to minimize wheel reinvention.

[Friskfisk]

Sounds great, I agree and I'm all for discussing in detail. I added it to the next Wednesday call agenda G doc, if that works for you for a first crack at seeing where we want to go with this?

[GMaynard1]

@jamespatrickmanning this is placemarker for you and I to sit down and go through the list of eMOLT contacts I've pulled together. Maybe we can do that remotely this week?