Here's a proposal for a single database which is a union of the existing SSDS and Instru databases. It should be possible to use a simple d/b view to translate this table to either the existing SSDS or instru formats without too many machinations. The goal is to minimize/eliminate redundancy by combining overlapping fields without drastically affecting the use of either of the existing apps (SSDS, Access app).

The steps to actually create the data in these fields are documented in MakingData.

This should be considered a phase one proposal to cover most of the "low hanging fruit" and start the integration process. Subsequent phases can further integrate the two databases while taking into consideration lessons learned from phase one.

A good example is the handling of people/roles. The instru database has a number of separate fields for different roles: owner/purchaser, technician, calibrator, etc. Each of these fields is just freetext, allowing for inclusion of multiple people for each role, also project names instead of peoples names. The SSDS database recognizes a single person, which is a record of type Person. Though it's not clear how this record was intended to be used, it does not map cleanly into the instru model.

Despite this data mismatch, it isn't strictly necessary to solve this conundrum for successful use of the integrated database. Later phases may include revisions to either or both data models (for example use of an associative array between ISO roles and Person records in SSDS).

Given the ability to "alias" fields when creating a view, name changes or mismatches should not be considered particularly serious.

Pending:

• Calibration information? This is a new addition and not really relevant to the "merge"
• should probably settle on a word formatting sheme (i.e. starts with lower case and capitalize to make subwords) – given we can alias when making the view it matters quite a bit less

SSDS Field	SSDS Comment	instru Field	instru comment	Merged field	Merge comment
SSDS ID	Auto-incrementing key field	SSDS ID	Used to link to entry in SSDS d/b	SSDS ID
UUID				UUID
mfgName		Manufacturer		mfgName	Needs some normalization
mfgModel		Model		mfgModel	Needs some normalization
mfgSerialNumber		Serial, FullSerial	One or both may be used. Typically FullSerial is the complete manufacturer's serial number and Serial is a shorter representation - often the actual sequential portion w/o any sort of model identification which may be encoded.	mfgSerialNumber, ShortSerial	Keep mfgSerialNumber as the full-length serial number.  Make it a mandatory field.  Allow an optional ShortSerial to hold a shortened serial number, which is a copy of the Instru Serial field.
name	Typically something like manufacturer+model			name
deviceType		Type		deviceType	Needs some normalization
Description	A free text description, may  contain some information redundant with name			Description
infoUrlList		manufacture web page		infoUrlList	Neither database uses this field very heavily. Best to merge into a single "place for a hyperlink" – though should we be specific that this is to either the manufacturers' home page or to the product page. Does it matter?
person	The contact person (link to the Person table)			person	Need to figure out how (if) to map the multiple instru roles into SSDS
		Purchased for	Sometimes a person, sometimes a project	Purchased for	"
		Owner	Typically some combination of person, group (OSG, etc) and project.  Sometimes all three	Owner	"
		Custodian	One or more people	Custodian	"
		Technician	Currently always 'pc' (Paul Coenen)	Technician	"
resources	Not used currently .  Could be used for storing calibration files?
		document dir	Not used currently
version				version
		Features	A free text description of features on the device	Features	Simplest to just leave this in place for now?
		Pressure sensor	Just freetext, left blank if irrelevant	Pressure sensor	"
		Depth Rating	Just freetext, left blank if irrelevant	Depth Rating	"
		Firmware/EPROM	Just freetext, left blank if irrelevant	Firmware/EPROM	"
		Memory	Just freetext, left blank if irrelevant	Memory	"
		Receive Frequency	Just freetext, left blank if irrelevant	Receive Frequency	"
		Transmit Frequency	Just freetext, left blank if irrelevant	Transmit Frequency	"
		Enable Code	Just freetext, left blank if irrelevant	Enable Code	"
		Release Code	Just freetext, left blank if irrelevant	Release Code	"
		Tilt option	Just freetext, left blank if irrelevant, though heavier use of "N/A" than in other fields	Tilt option	"
		Date new	Used infrequently	Date new	"
		Calibration organization	Typically a company, but sometimes a company and a person	Calibration organization	In the long run we might normalize this against the msgNames
		PO		PO	"
		Permanent comment	Not entirely sure what this field is used for. I think it's meant to be some permanent and overarching state of the system, not just the latest "transaction"	??	Keep it? Or discard?
		MBARI_ID	Auto-incrementing key field	(discard)	Redundant if the trans table in instru is rewritten using SSDS IDs instead
		Transaction	I believe this field isn't used in instru	(discard)