0

I need to save around 400 items of information on a database.

Something similar to the following (I have a few tables like this):

create table dbo.Details
(
  Id int not null

  ProductTypeIsBook bit not null,
  ProductTypeIsFood bit not null,
  ProductTypeIsCloth bit not null,
  ProductTypeIsToy bit not null,
  ProductTypeIsOther nvarchar (200) null,

  ProductColorIsRed bit not null,
  ProductColorIsGreen bit not null,
  ProductColorIsYellow bit not null,
  ProductColorIsOther nvarchar (200) null

)

So ProductType can have only one true or no trues. And Other can have value or not.

In case of ProductType can all be true and Other can be null or not ...

When having 400 columns like this in a few tables it is difficult to move everything to lookup tables ... I would end up with a lot of them the the joins by create problems with performance ...

What do you think about the solution I present here? Any suggestion?

Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
Miguel Moura
  • 36,732
  • 85
  • 259
  • 481
  • 2
    [Sounds like a case for EAV](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx) - read the article before listening to all the doomsday talk about how it's the worst thing you can possibly do... – Aaron Bertrand Sep 02 '14 at 21:17
  • I agree that this sounds like a great case for an EAV pattern. As Aaron says there is lots of doomsday talk out there. Often times this type of pattern can get used in the wrong situations, this to me seems like a perfect example of when it is in fact the best choice. – Sean Lange Sep 02 '14 at 21:28
  • Pertinent: http://stackoverflow.com/questions/2224234/database-eav-pros-cons-and-alternatives – nshah Sep 02 '14 at 21:34
  • Can you group the 400 columns by type? How many are bit, int, etc? – nshah Sep 02 '14 at 21:40
  • Interesting ... I suppose EAV would be a great choice but I preview a lot of problems when using ORMs and EAV. Am I wrong? Did anyone ever used EAV with, for example, Entity Framework? – Miguel Moura Sep 04 '14 at 09:13

0 Answers0