Assigning extra information to all database table fields

I'm an experienced desktop app programmer, but fairly new to database programming. I'm working with C#/SQL right now. I understand the concept of a lookup table, as in storing a two-character state (such as CA) in a field in one table, and then being able to get the full state name (such as California) from a second table:

Table: Address
AddressID int <PK>
StateCode char(2) <FK>
...

Table: State
StateCode char(2) <PK>
StateName varchar(25)

My question: is there some similar way to be able to provide just about every field of every table with a means of holding common, extra information? For instance, let's say I wanted to store, say, an extended name and some special code with every field in three tables. As in this simplistic example:

Table: A
Field1 int
Field2 varchar(20)

Table: B
Field1 bit
Field2 int

Table: C
Field1 int

Table: ExtraInfo
LongName varchar(50)
TypeCode int

If I wanted to be able to have the information in the ExtraInfo table associated with each field in Table A, Table B and Table C, how would I do that?

Thanks!

I'm not sure that I understand exactly what you are trying to do.

Might you want a table "ExtraInfo" with columns

Table_Name varchar(50),

Column_Name varchar(50),

Extra_Info varchar(50),

TypeCode int

Maybe Extra_Info could be something like a column description?

Is that the right idea?

Dan

|||I'm not totally clear about the concept. Perhaps looking in Books Online for 'Extended Properties' would prove to be helpful.|||

Just to add my 3 cents worth to the interrogation, are you wanting to store metadata? Or are you trying to store information for use by other users? That would make a big difference.

Also, if you are trying to be generic, like having a base class, it is seldom a good idea, even if it seems like a good idea at this point. SQL is centered around implementing a specific design, not genericness (genericism, genericisticy?)

|||

Louis -

Yes, metadata. No user-entered information.

And no, I'm not looking to be generic.

The information would be very specific. As in, say, being able to provide every field of any (or all) tables with a string title (that differs from the field's column name). Or any other bit of information that might be useful for all fields. For the sake of a solution, ignore the exact type of information (metadata) that's to be tracked. It could be anything. Let's say I want to assign one of three colors (red, yellow or green) to every field of every table. The data can be anything. I just want to know how I could store some common, non-generic data to every field. To, in a sense, make every field of any table a structure (to put it in app programming terms). It seems like there should be some standard way of doing this. It's probably my desktop app programming background limiting my explanation.

Thanks!

|||

DanR1 -

I think this might be a solution. I'll look into it. If the one table had TableName and ColumnName fields as you suggest, then it could have any other fields for the metadata, and the TableName and ColumnName fields provide a way back to each particular field of any table. That might work. I'm not sure if that's a "legitimate" solution as far as database design goes (as mentioned, I'm not a big DB programmer), but it is certainly on the right track for what I'm trying to do.

Thanks

|||

Arnie -

I've googled, searched Safari Online, everything. Nothing.

From a programming perspective (which I think in terms of), this is easy. For instance, in OOP there could be a base class Animal that keeps track of an animal's color. Then, any classes of particular animals could inherit the Animal class (and thus each animal could hold color information) as well as keep track of information particular to that animal:

class Animal
{
int color;
}

class Dog : Animal
{
bool chasesCars;
}

class Pig : Animal
{
bool likesMud;
}

The above, in DB terms, would be a table Dog with one bit field and a table Pig that also had a bit field. Besides that one piece of information though, each of these fields could also keep track of a color. That's the kind of "extra" information I'm trying to tie to each field.

Thanks

|||

It sounds like you just need to add additional columns to the tables. Such as [DescriptiveTitle], [DefaultValue], [MyMetaData], etc.

It is very common for additional metadata columns to be added to tables. For example, in databases I create, all tables have columns [InsertedBy], InsertedDate], [ChangeBy], [ChangeDate]. These columns are rarely used by applications, but are for administrative and auditing purposes. You have different needs, but the solution is similar.

Creating a 'metadata' table seems overly cumbersome and frought with peril.

|||

Dan Parks,

I'd have to agree with Arnie that it sounds like the database solution for what you are wanting to do would be to add columns to the table, and allow them to have NULL entries for cases where you did not set a value (such as the COLOR of your DOG or PIG).

Although it would be possible to use the Binary column type (I don't remember the exact name for it, but it is what allows JPG, BMP, etc., data to be stored in a table -- almost always by storing a LINK to the place where the database actually stores the large binary data values -- almost never in the row of data in the table) and store therein a Structure such as you might use in C++, you would then have to write your own procedures to unpack that structure if you want to search it for certain values, or if you want to change certain values. That seems to be avoiding many of the benefits of having a database with your ability to add columns as needed, and to JOIN tables on columns, and search for values in columns, etc.

Before I became involved with SQL and databases most of my programming had been in FORTRAN. Our data were stored in sorted fixed-record-length data files. We would have to perform binary searches to find the records we needed to perform a computation. We would have to create our own accumulator variables to sum quantities. If a coworker wanted to find all entries wherein a data column had a certain value, we would have to write a special program to search the entire file for that value, and display the records where it was found. If he wanted to search for multiple potential values in that column, we would have to write the program to allow for multiple entries. Now it is so much easier:

select *

from TABLE

where COLUMN1 in (value1, value2, value3)

order by 1,2,3,4,5

SQL has made much of my earlier programming rather trivial in terms of the SQL necessary to perform the same tasks. And because of the simpler SQL code (simpler to write, simpler to maintain) we were able easily to improve the computations to deal with nuances in the data that seemed awfully ugly in the FORTRAN solution. (Imagine a complex WHERE clause, relying on a number of subqueries of the same data -- easy in SQL, rather ugly in FORTRAN.)

Dan