Mastodont Posted March 21, 2009 Share Posted March 21, 2009 What route is best to go if I need design tables for entities with arbitrary numbers of attributes? I know that is called EAV (entity - attribute - value) and tried a Google, but missed answer for actual table design. If we have arbitrary attributes with various data types, there are three ways to go: 1. item_id - attribute - value // one table, values with different data types in third column 2. item_id - attribute - value // one table for each data type 3. item_id - attribute - string - integer - float - date ... //one column for each data type (I see this in BEA WebLogic) First option is terrible. But the others look fine (?). Downsides of second choice include complicated selects and writing, third choice leads to "Swiss cheese" table. Has anybody experiences with EAV? Quote Link to comment Share on other sites More sharing options...
Mchl Posted March 21, 2009 Share Posted March 21, 2009 I'd probably go with #2. Makes most sense to me. #1 also might work. MySQL has several functions, that help in type juggling. #3 has the least appeal to me. Having table filled mostly with nulls is just too inefficient... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.