Is it possible to have a relationship from a user table to a system table view? To give context, I would like the values of a column in one of my tables to be restricted to the column names of another one of my tables, which seems easiest to do by going through the system view that houses the column names of the second table.
So, using the classic example, if I had a table of Customers (FirstName LastName), I would like to create another table that had a column "customerAttribute" which could only be "FirstName" or "LastName." To keep this dynamic, it would be nice if the "customerAttribute" column was actually a foreign key from the system view that stores the names of the columns in the Customer table; that way, I don't have to worry about data integrity issues as columns are added, deleted, and renamed in the actual Customers table.
I didn't see an easy way to create this relationship in SQL Server, so I'm wondering if messing with/creating relationships with system tables and/or views is a major no-no.
Thanks!
P.S. I ask this question to help me solve another problem I've posted on SO.
Edit: Even if you cannot directly make a relationship to a system view, perhaps you could create a view that returned the query to the system view (to get the column names) and then made a relationship to that view... I'll try that now.
There's obviously nothing to prevent you from creating a user table column that references a system table column. So the obvious answer is yes. To answer your real question it's necessary to ask a different question, namely: is it a good idea to store metadata in user tables?
If I were to answer from a data management purists point of view, I'd say that this is nearly always a bad idea. As a practical matter, however, I've done it even though I'm not proud of it. There are some results you can get by blending data and metadata that are nearly impossible to acheive without blending them.
The risk you run is that eventually you'll end up with a database that can't be documented in terms of attributes that make sense to subject matter experts. In other words, your database will only be useable by yourself and your fellow geeks. Sometimes that's an acceptable risk. Sometimes it isn't.