1

I have a table with a large number of very specific columns which are mostly "Strings". I believe this hardcoding of names will give me grief later on when rules changes. I am investigating the option of using a second table where each row is a column from the master table which could each have a lookup key for its name.

I know this may seem anti ER best practice, but it would be flexible. I could use views with subselects ie Select (SQL1), (SQL2) etc from Table1. Not sure if one can update a multitable view though in SQLServer.

Thoughts on the above greatly appreciated.

Thanks,

Ed

EdB
  • 449
  • 6
  • 21

3 Answers3

3

It sounds like what you're looking for is something like an Entity-Attribute-Value (EAV) table.

EAV allows for a much more dynamic process when it comes to being flexible with regards to allowing further customization down the line, but poor implementation can mean that it doesn't adhere well to the relational model. This SO question provides a good rundown of some of the issues inherent with that kind of solution.

You'd probably be better off refactoring your columns to use a less context-specific naming convention.

Community
  • 1
  • 1
mikurski
  • 1,353
  • 7
  • 20
  • This is fascinating. My problem domain is about collecting attribute data for a report with multiple sections with some common attributes and other not so. Ideally the data model should support a seperate "report domain". The flip side of the this type of design can be that the application layer get more complicated, or perhaps I am worrying unduly. My application technology will be ASP.NET MVC 3. I am going to read up on this pattern. – EdB Oct 18 '12 at 23:47
  • "You'd probably be better off refactoring your columns to use a less context-specific naming convention." I get what you mean, but I would lose the meaning of the column. I could have C1, C2, C3, but I guess I would need to store somewhere what C1,C2,C3 meant for this record type. – EdB Oct 18 '12 at 23:55
  • "I would need to store somewhere what C1,C2,C3 meant for this record type." Exactly! The key with SQL is that the database stores information about specific entities (customers, products) and the relationships between those entities (when a customer buys a product). When I say 'context-specific', I mean you'd be better off not using names tied to an external standard - you want to use generic names for your tables and columns that hold meaning for the data in that column. If you have data that's too varied, denormalize and remember that you can't account for everything all the time. – mikurski Oct 19 '12 at 00:18
  • Just had a look into EAV. It does looks as if one would be creating issues since one is effectively fight what the RDBMS was designed for, well that is if one is using SQL Server and not SimpleDB. – EdB Oct 19 '12 at 00:33
  • With regard to the "C1,C2" comment, I am thinking that one could have a "C1,.....C20", use as many as required for the context and then have a metatable to hold the meaning of C1....Cn. So C1... Cn are just data pots for the record. – EdB Oct 19 '12 at 00:36
  • Lacking in more specifics, I can't give you much more in the way of feedback. You may want to ask for more specific critique of the details of your schema in another question. – mikurski Oct 19 '12 at 00:38
  • Other than that, all I can really suggest is that you look at the 'meaning' of what C1...C20 contain, and see if any of those can be consolidated into separate tables. – mikurski Oct 19 '12 at 00:39
1

Avoid the inner platform effect. SQL Server already gives you the ability to query which columns are in a table. See sys.tables and sys.columns.

Use these to query which columns you have, and use standard DDL commands to add and remove columns as needed. Dont be afraid of normalization, joins, and the like.

The "database within a database" almost always results in tears down the road when you need to do something that would be trivial in a normal design.

Chris Shain
  • 50,833
  • 6
  • 93
  • 125
  • Thanks for the promptness of reply. I take you point about the "Inner Platform Effect", however I am failing to understand how the use of the sys.tables removes the need to specify very specific column names. I am probably being abit slow...:) – EdB Oct 18 '12 at 23:49
1

You can store the column as type SQL_VARIANT.
It's not the best practice in the world but you can use it.

create table t  (anything sql_variant);
insert t values (current_timestamp);
insert t values (current_timestamp+1);
insert t values (1);
insert t values ('some text');
insert t values (current_timestamp-3);
insert t values (null);
insert t values (2.1234);
insert t values (cast(2 as decimal(10,5)));
insert t values ('some more text');

-- sample based on type
select *
  from t
 where CAST(sql_variant_property(anything, 'BaseType') as varchar(20)) like '%char';

From your question, you'd be storing the type stored in another column or from a linked table column.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Thanks for this. I never knew about the SQL_VARIANT data type. It does feel as if I am breaking all the rules with this one!! – EdB Oct 18 '12 at 23:50