0

I have three tables that look like 

table1
ID    Title      Description  
1     Title1     Desc1   
2     Title2     Desc2

table2
ID    HistoryID
1     1001
2     1002
2     1003
2     1004

table3
HistoryID Value
1001      val1
1002      val2
1003      val3
1004      val4

Now I am planning to it using "only" two tables

table1
ID    Title      Description     HistoryIDList
1     Title1     Desc1           1001
2     Title2     Desc2           1002,1003,1004

table3
HistoryID Value
1001      val1
1002      val2
1003      val3
1004      val4

I have created a sql table-value function that will return indexed values 1002,1003,1004 that could be joined with HistoryID from table3.

Since I am losing normalization, and do not have FK for HistoryIDList, my questions are

  • should there by significant performance issue running a query that would join HistoryIDList
  • would indexing sql function do the trick or not since there is no relation between two columns.

In that case is it possible to add FK on table created in sql function?

mko
  • 6,638
  • 12
  • 67
  • 118
  • 1
    Why do you want to do this? – Mikael Eriksson Jan 10 '14 at 20:45
  • @MikaelEriksson i wad thinking of using just two tables.. – mko Jan 10 '14 at 20:48
  • Ok, but what would be the benefit of doing that? – Mikael Eriksson Jan 10 '14 at 20:49
  • @MikaelEriksson easier data manipulation, less space required etc. If those reasons are not good enough for such a move, how about if I use used XML column in order to store HistoryIDList in XML format? – mko Jan 10 '14 at 21:00
  • 1
    Have a look at [this](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Mikael Eriksson Jan 10 '14 at 21:57
  • @MikaelEriksson i completely agree, but there is a open window for xml in the last post, right? At least for some cases.. – mko Jan 10 '14 at 22:10
  • "With XML: values in a tag can be forced to be the correct type" only if if the XML column is defined using a schema. – Mikael Eriksson Jan 10 '14 at 22:17
  • "With XML: xml field can be indexed" the xml index that exist prior to SQL server 2012 will probably not help much with joins. I have not tested that. The index takes a lot of space and is persisted in system tables. I have yet to find a scenario where such index is helpful. – Mikael Eriksson Jan 10 '14 at 22:21
  • In SQL server 2012 there exist selective xml indexes. Those might be helpful for joins but they are also persisted in a system table much wider than the table you already have. But with the correct index definition it is way better than the regular XML index both in size on disk and performance. But it will not come close to a table specifically designed to support what you want to do. – Mikael Eriksson Jan 10 '14 at 22:24
  • If I get to setup something that compares performance using the different XML indexes and a regular table I will post my findings as an answer. – Mikael Eriksson Jan 10 '14 at 22:28

1 Answers1

3

Why would you change a good data structure to a bogus data structure? The two table version is a bad choice.

Yes, there is a significant performance difference when joining from a single id to a list, compared to a simple equi-join. And, as bad as that normally is, the situation is even worse here because the type of the id is presumably an int in the original table and a character string in the other table.

There is no way to enforce foreign key relationships with your proposed structure without using triggers.

The only thing that you could possibly do to improve performance would be to have a full text index on the HistoryIdList column. This might speed the processing. Once again, this is complicated by the fact that you are using numeric ids.

My recommendation: just don't do it. Leave the table structure as it is, because that is the best structure for a relational database.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I agree with you. Basically, comma delimited content should be used (if used) only to store data that would not be used with join or where.. What about if I used XML column to store HistoryIDList in XML format? – mko Jan 10 '14 at 21:03
  • @John . . . That is not going to make the joins more efficient. – Gordon Linoff Jan 10 '14 at 21:06
  • I am hipotetically speaking... Not even after indexing XML column? http://stackoverflow.com/questions/266556/how-can-i-join-an-xml-column-back-onto-the-record-it-originates-from – mko Jan 10 '14 at 21:09
  • @John . . . I do not know how the XML indexes perform when you have multiple identical keys in the XML. – Gordon Linoff Jan 10 '14 at 21:14
  • I understand, but where do you find identical keys in the example above? – mko Jan 10 '14 at 21:21