[dba-SQLServer] Fwd: Database of databases (Field Types)

[David McAfee]

Sorry for the cross posts, I just wanted to get as many possible opinions
as possible...

I have a database that I use for my SSIS procedure where we import records
from various Access DBs into a SQL Server "mini data warehouse"

I'm trying to compare fields for such conditions such as "added",
"removed", "type change" or "size increase", so I can flag the field and
trap for it at import time.

Over the years, some of the tables have had fields added to them and some
removed.
When removed, I currently leave them in the SQL version, and bring them in
as null.

I don't know whether or not I should add a boolean "DontUse" field as I do
for theMDBs for fields in the new tables that I am adding. I am also not
sure if I should do it in tblFields or tblfieldsInTable (See below).

Another idea that I am playing with is to add a "DateRemoved" column
instead of a boolean, then I kind of have a historical record of when it
was added and when it was removed.

If I use this dateRemoved approach, should I re-add the field if it ever
gets added back (should never happen)? I was also thinking of adding
another table to deal with field status (added/removed), but I think that
is over complicating things.

With this design, I will have duplicate field names if it increases in size.
Let's say ClientName_First is a text (35) and down the road it changes to a
text (50) then later on maybe shortened to a text(25). I will only have the
records for text (35) and text (50). I will always group and grab max size
when comparing, so I will always look for ClientName_First with a text(>50)
or of a different type. I could further normalize table Fields and break
out the size and type to a different table, but again, I'm not sure if the
added complication is worth it.

Any thoughts/comments?

I have the following tables:
tblMDBs (a table of the source databases that are imported)
MDBID (PK)
MDBPath
MDBName
DateModified
Version
DoNotImportToSQL
entryDate

tbltables (a table of Tables)
tblID
tblName
entryDate

tblMDBTableJunct 9Junction table to match the tables to databases)
JunctPKID,
MDBID,
tblID

The new fields & tables that I am adding are:
tblFields (Field names and types)
FieldID
FieldName
FieldType
FieldSize

tblfieldsInTable (a junction table that links the field to the table (and
database))
FITPKID (PK)
JunctPKID (FK)
FieldID (FK)
ErrorFlagID (FK)
entryDate


tblErrorFlags
ErrorFlagID
ErrorFlagDescription


these are the error flags:
ErrorFlagID ErrorDescription
1 Field type has changed
2 Field size is larger
3 The field has been removed
4 A new field has been added