BACKGROUND:
I am using MS SQL 2000 in case that matters.

I work with a fairly large database that tracks product information.
There are currently hundreds of tables with attribute data, each table
grouping a specific product family (say one table with circuit
breakers, contactors, another with pushbuttons, etc.).

Till now, each table was defined with the columns as the attribute
headers.
Part Number, Ampere Rating, Voltage Rating, etc.
xxxx, 10A, 120V, etc.

Each table may have 20 to 40 attributes, many of them are used
repeatedly in other tables... about 600 attribute labels in use.

The problem is those column names sometimes need to change, Ampere
Rating to Current Rating etc. It has become tough to manage.
Attributes columns come and go very often.

I would like to manage all the attributes in a central table:
Attribute_ID, Attribute_Label
101, Ampere Rating

And changeover all the product tables to reference the ID as the
column name. I figure this way I can change the label in one place
(as well as several other concepts suffix, prefix, alternate labels,
etc.) and keep things consistent.

THE QUESTION:
When exporting tables to Excel or editing them directly in Enterprise
Manager (or querying in Query Manager) I want to see the
Attribute_Label as the column headers. Outside of hard coding a view
(and a static query) for each of the 300 tables, for each column to
have an alias.... select attrib_001 as 'Ampere Rating', select
attrib_002 as 'Voltage Rating', which defeats the purpose.

I can't find a way to link in the attribute label from the central
table.
I know it won't be as easy as
SELECT * AS (SELECT attribute_label FROM attribute_table) FROM
circuit_breakers

I hope this makes sense, I am sure I am not using the proper words. I
guess I am looking for relational column aliases?

If any one has any ideas on this specifically or at a higher level (I
am looking to totally revamp the database structure) plase let me
know.

Halgar (tm_tech32 (AT) yahoo (DOT) com) writes:
First: I think that using a numeric id is not a good idea, because
it is likely that you or someone else will sooner or later confuse
the numbers. Rather, I think it would be better to use mnemonic names,
maybe based on the current names.

The obvious solution would the be to define the tables from current
names, and don't change these names but keep them. Then you define
views from your mapping tables. These views would be generated from
this table. This could be done in a stored procedure, or it could be
done by a small program in the language of your choice: C#, VBscript,
Perl or whatever.

The drawback of using a stored procedure is that of the languages out
there, most languages have better support for string manipulation than
T-SQL has. Then again, a stored procedure could be started by a trigger
on the mapping table, making the process a little more automatic.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Sep 10, 4:24*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
"Then you define views from your mapping tables. These views would be
generated from this table. This could be done in a stored procedure"

I guess that is more specifically what I am asking for help with. The
only way I can see to create a view, where a column name uses an
alias, is to hard code the text alias in the view creation.
How would I pull the alias value (or am I looking for an alternative
method) from another table?

SELECT ampere_rating AS (SELECT attribute_label FROM attribute_table
WHERE attribute_id = 'ampere_rating') FROM circuit_breaker_table

That would seem to be the logic but I can't get it to work, I have
tried unsuccessfully to wrap it in an EXEC line.

Halgar (tm_tech32 (AT) yahoo (DOT) com) writes:
Yes, that is how the final views would look like. But my point is that you
would generate the views.

Like you would read any table:

SELECT alias_name, table_coumn, table_name
FROM mapping_table
ORDER BY table_name

Then start a new CREATE VIEW statement each time you get a new table name.
And execute the one you have just completed.

But why use T-SQL? T-SQL is definitely not the best choice for this task.
Sure you can do it. Put the above in a cursor and build the strings. But
there are legions of languages out there that are better fitted to build
strings in.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx