- Multiple Child Tables
- Single "Extension" table
EVA
table- Multiple Child Tables
- Single "Extension" table
My initial response was the EVA
design method, however I don't think that is the solution you want to go with. My concern is when it comes to tryingThanks to SELECT
data out ofsome insight from Jon the EVA
table. You would need to do 16is certainly a very viable approach with proper indexing using a LEFT OUTER JOINPIVOT
's each time you wanted to get all 16
For the sake of the example on the OPTIONSPIVOT
(since I was not familiar with it) I created a simple EAV
table. Potentially something like this:
DECLARE @Option1Default Decimal(10,5), --Or Whatever the appropriate datatype is
@Option2Default Decimal(10,5),
@Option3Default Decimal(10,5),
@Option4Default Decimal(10,5),
@Option5Default Decimal(10,5),
@Option6Default Decimal(10,5),
@Option7Default Decimal(10,5),
@Option8Default Decimal(10,5),
@Option9Default Decimal(10,5),
@Option10Default Decimal(10,5),
@Option11Default Decimal(10,5),
@Option12Default Decimal(10,5),
@Option13Default Decimal(10,5),
@Option14Default Decimal(10,5),
@Option15Default Decimal(10,5),
@Option16Default Decimal(10,5)
SET @Option1Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 1')
SET @Option2Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 2')
SET @Option3Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 3')
SET @Option4Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 4')
SET @Option5Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 5')
SET @Option6Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 6')
SET @Option7Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 7')
SET @Option8Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 8')
SET @Option9Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 9')
SET @Option10Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 10')
SET @Option11Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 11')
SET @Option12Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 12')
SET @Option13Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 13')
SET @Option14Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option@Table 14')TABLE
SET @Option15Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 15')
SET @Option16Default = (SELECT SystemDefaultValue FROM Options WHERE [Description] = 'Option 16')
SELECT PYDC.Oid,
CASE
WHEN PYDCOC1.CustomValue IS NULL THEN @Option1Default
ELSE PYDCOC1.CustomValue
END AS Option1Value,
CASE
WHEN PYDCOC2.CustomValue IS NULL THEN @Option2Default
ELSE PYDCOC2.CustomValue
END AS Option2Value,
CASE
WHEN PYDCOC3.CustomValue IS NULL THEN @Option3Default
ELSE PYDCOC3.CustomValue
END AS Option3Value,
CASE
WHEN PYDCOC4.CustomValue IS NULL THEN @Option4Default
ELSE PYDCOC4.CustomValue
END AS Option4Value,
CASE
WHEN PYDCOC5.CustomValue IS NULL THEN @Option5Default
ELSE PYDCOC5.CustomValue
END AS Option5Value,
CASE
WHEN PYDCOC6.CustomValue IS NULL THEN @Option6Default
ELSE PYDCOC6.CustomValue
END AS Option6Value,
CASE
WHEN PYDCOC7.CustomValue IS NULL THEN @Option7Default
ELSE PYDCOC7.CustomValue
END AS Option7Value,
CASE
WHEN PYDCOC8.CustomValue IS NULL THEN @Option8Default
ELSE PYDCOC8.CustomValue
END AS Option8Value,
CASE
WHEN PYDCOC9.CustomValue IS NULL THEN @Option9Default
ELSE PYDCOC9.CustomValue
END AS Option9Value,
CASE
WHEN PYDCOC10.CustomValue IS NULL THEN @Option10Default
ELSE PYDCOC10.CustomValue
END AS Option10Value,
CASE
WHEN PYDCOC11.CustomValue IS NULL THEN @Option11Default
ELSE PYDCOC11.CustomValue
END AS Option11Value,
CASE
WHEN PYDCOC12.CustomValue IS NULL THEN @Option12Default
ELSE PYDCOC12.CustomValue
END AS Option12Value,
CASE
WHENID PYDCOC13.CustomValueINT ISNOT NULL THEN @Option13Default
ELSE PYDCOC13.CustomValue
END AS Option13Value,
CASE
WHENOptionID PYDCOC14.CustomValueINT ISNOT NULL THEN @Option14Default
ELSE PYDCOC14.CustomValue
END AS Option14Value,
CASE
WHEN PYDCOC15.CustomValue IS NULL THEN @Option15Default
ELSE PYDCOC15.CustomValue
END AS Option15Value,
CASE
WHEN PYDCOC16.CustomValue[Value] ISINT NULL THEN @Option16Default
ELSE PYDCOC16.CustomValue
END AS Option16Value
FROM ProductYearDistrictCrop PYDC
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC1
ON PYDC.Oid = PYDCOC1.Oid --assuming that is the FK on ProductYearDistrictCropOptionChoices that references ProductYearDistrictCrop.Oid
AND PYDCOC1.OptionID = 1
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC2
ON PYDC.Oid = PYDCOC2.Oid
AND PYDCOC2.OptionID = 2
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC3
ON PYDC.Oid = PYDCOC3.Oid
AND PYDCOC3.OptionID = 3
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC4
ON PYDC.Oid = PYDCOC4.Oid
AND PYDCOC4.OptionID = 4
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC5
ON PYDC.Oid = PYDCOC5.Oid
AND PYDCOC5.OptionID = 5
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC6
ON PYDC.Oid = PYDCOC6.Oid
AND PYDCOC6.OptionID = 6
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC7
ON PYDC.Oid = PYDCOC7.Oid
AND PYDCOC7.OptionID = 7
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC8
ON PYDC.Oid = PYDCOC8.Oid
AND PYDCOC8.OptionID = 8
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC9
ON PYDC.Oid = PYDCOC9.Oid
AND PYDCOC9.OptionID = 9
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC10
ON PYDC.Oid = PYDCOC10.Oid
AND PYDCOC10.OptionID = 10
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC11
ON PYDC.Oid = PYDCOC11.Oid
AND PYDCOC11.OptionID = 11
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC12
ON PYDC.Oid = PYDCOC12.Oid
AND PYDCOC12.OptionID = 12
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC13
ON PYDC.Oid = PYDCOC13.Oid
AND PYDCOC13.OptionID = 13
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC14
ON PYDC.Oid = PYDCOC14.Oid
AND PYDCOC14.OptionID = 14
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC15
ON PYDC.Oid = PYDCOC15.Oid
AND PYDCOC15.OptionID = 15
LEFT OUTER JOIN ProductYearDistrictCropOptionChoices PYDCOC16
ON PYDC.Oid = PYDCOC16.Oid
AND PYDCOC16.OptionID = 16
--WHERE Condition(s)
What I am not overly fond ofthen populated it with 16 unique ID
's and each configured for 16 different OptionID
's. The value is populated with the ideaproduct of having to make 16 JOINSID
toand OptionID
If those two values are the same table. You just run intothen no value was provided (If ID <= 6 a high potential for locking onvalue of NULL
was provided, else no row was added (so that we got examples for both a NULL
value and a missing row)).
INSERT INTO @Table (ID, OptionID, Value)
VALUES (1, 1, NULL),
(1, 2, 2),
(1, 3, 3),
(1, 4, 4),
(1, 5, 5),
(1, 6, 6),
(1, 7, 7),
(1, 8, 8),
(1, 9, 9),
(1, 10, 10),
(1, 11, 11),
(1, 12, 12),
(1, 13, 13),
(1, 14, 14),
(1, 15, 15),
(1, 16, 16),
(2, 1, 2),
(2, 2, NULL),
(2, 3, 6),
(2, 4, 8),
(2, 5, 10),
(2, 6, 12),
(2, 7, 14),
(2, 8, 16),
(2, 9, 18),
(2, 10, 20),
(2, 11, 22),
(2, 12, 24),
(2, 13, 26),
(2, 14, 28),
(2, 15, 30),
(2, 16, 32),
(3, 1, 3),
(3, 2, 6),
(3, 3, NULL),
(3, 4, 12),
(3, 5, 15),
(3, 6, 18),
(3, 7, 21),
(3, 8, 24),
(3, 9, 27),
(3, 10, 30),
(3, 11, 33),
(3, 12, 36),
(3, 13, 39),
(3, 14, 42),
(3, 15, 45),
(3, 16, 48),
(4, 1, 4),
(4, 2, 8),
(4, 3, 12),
(4, 4, NULL),
(4, 5, 20),
(4, 6, 24),
(4, 7, 28),
(4, 8, 32),
(4, 9, 36),
(4, 10, 40),
(4, 11, 44),
(4, 12, 48),
(4, 13, 52),
(4, 14, 56),
(4, 15, 60),
(4, 16, 64),
(5, 1, 5),
(5, 2, 10),
(5, 3, 15),
(5, 4, 20),
(5, 5, NULL),
(5, 6, 30),
(5, 7, 35),
(5, 8, 40),
(5, 9, 45),
(5, 10, 50),
(5, 11, 55),
(5, 12, 60),
(5, 13, 65),
(5, 14, 70),
(5, 15, 75),
(5, 16, 80),
(6, 1, 6),
(6, 2, 12),
(6, 3, 18),
(6, 4, 24),
(6, 5, 30),
(6, 6, NULL),
(6, 7, 42),
(6, 8, 48),
(6, 9, 54),
(6, 10, 60),
(6, 11, 66),
(6, 12, 72),
(6, 13, 78),
(6, 14, 84),
(6, 15, 90),
(6, 16, 96),
(7, 1, 7),
(7, 2, 14),
(7, 3, 21),
(7, 4, 28),
(7, 5, 35),
(7, 6, 42),
--(7, 7, NULL),
(7, 8, 56),
(7, 9, 63),
(7, 10, 70),
(7, 11, 77),
(7, 12, 84),
(7, 13, 91),
(7, 14, 98),
(7, 15, 105),
(7, 16, 112),
(8, 1, 8),
(8, 2, 16),
(8, 3, 24),
(8, 4, 32),
(8, 5, 40),
(8, 6, 48),
(8, 7, 56),
--(8, 8, NULL),
(8, 9, 72),
(8, 10, 80),
(8, 11, 88),
(8, 12, 96),
(8, 13, 104),
(8, 14, 112),
(8, 15, 120),
(8, 16, 128),
(9, 1, 9),
(9, 2, 18),
(9, 3, 27),
(9, 4, 36),
(9, 5, 45),
(9, 6, 54),
(9, 7, 63),
(9, 8, 72),
--(9, 9, NULL),
(9, 10, 90),
(9, 11, 99),
(9, 12, 108),
(9, 13, 117),
(9, 14, 126),
(9, 15, 135),
(9, 16, 144),
(10, 1, 10),
(10, 2, 20),
(10, 3, 30),
(10, 4, 40),
(10, 5, 50),
(10, 6, 60),
(10, 7, 70),
(10, 8, 80),
(10, 9, 90),
--(10, 10, NULL),
(10, 11, 110),
(10, 12, 120),
(10, 13, 130),
(10, 14, 140),
(10, 15, 150),
(10, 16, 160),
(11, 1, 11),
(11, 2, 22),
(11, 3, 33),
(11, 4, 44),
(11, 5, 55),
(11, 6, 66),
(11, 7, 77),
(11, 8, 88),
(11, 9, 99),
(11, 10, 110),
--(11, 11, NULL),
(11, 12, 132),
(11, 13, 143),
(11, 14, 154),
(11, 15, 165),
(11, 16, 176),
(12, 1, 12),
(12, 2, 24),
(12, 3, 36),
(12, 4, 48),
(12, 5, 60),
(12, 6, 72),
(12, 7, 84),
(12, 8, 96),
(12, 9, 108),
(12, 10, 120),
(12, 11, 132),
--(12, 12, NULL),
(12, 13, 156),
(12, 14, 168),
(12, 15, 180),
(12, 16, 192),
(13, 1, 13),
(13, 2, 26),
(13, 3, 39),
(13, 4, 52),
(13, 5, 65),
(13, 6, 78),
(13, 7, 91),
(13, 8, 104),
(13, 9, 117),
(13, 10, 130),
(13, 11, 143),
(13, 12, 156),
--(13, 13, NULL),
(13, 14, 182),
(13, 15, 195),
(13, 16, 208),
(14, 1, 14),
(14, 2, 28),
(14, 3, 42),
(14, 4, 56),
(14, 5, 70),
(14, 6, 84),
(14, 7, 98),
(14, 8, 112),
(14, 9, 126),
(14, 10, 140),
(14, 11, 154),
(14, 12, 168),
(14, 13, 182),
--(14, 14, NULL),
(14, 15, 210),
(14, 16, 224),
(15, 1, 15),
(15, 2, 30),
(15, 3, 45),
(15, 4, 60),
(15, 5, 75),
(15, 6, 90),
(15, 7, 105),
(15, 8, 120),
(15, 9, 135),
(15, 10, 150),
(15, 11, 165),
(15, 12, 180),
(15, 13, 195),
(15, 14, 210),
--(15, 15, NULL),
(15, 16, 240),
(16, 1, 16),
(16, 2, 32),
(16, 3, 48),
(16, 4, 64),
(16, 5, 80),
(16, 6, 96),
(16, 7, 112),
(16, 8, 128),
(16, 9, 144),
(16, 10, 160),
(16, 11, 176),
(16, 12, 192),
(16, 13, 208),
(16, 14, 224),
(16, 15, 240)--,
--(16, 16, NULL)
The table can then be queried with a PIVOT
command. (As I understand it the PIVOT
requires that we use an Aggregate
function SUM
, particularly if this queryMIN
, MAX
, etc.... If there is used a lot.constraint added so that the IndexingID-OptionID
cancombination is unique we shouldn't get you around a lot of itany incorrect outputs. I just arbitrarily chose SUM
)
I am not familiar with how well the PIVOT
function performs but it is a built-in function so I would imagine performance is pretty good.
SELECT ID,
[1] AS Option1, --SUM([Value]) where OptionID = 1 Grouped By ID
[2] AS Option2, --SUM([Value]) where OptionID = 2 Grouped By ID
[3] AS Option3, --SUM([Value]) where OptionID = 3 Grouped By ID
[4] AS Option4, --SUM([Value]) where OptionID = 4 Grouped By ID
[5] AS Option5, --SUM([Value]) where OptionID = 5 Grouped By ID
[6] AS Option6, --SUM([Value]) where OptionID = 6 Grouped By ID
[7] AS Option7, --SUM([Value]) where OptionID = 7 Grouped By ID
[8] AS Option8, --SUM([Value]) where OptionID = 8 Grouped By ID
[9] AS Option9, --SUM([Value]) where OptionID = 9 Grouped By ID
[10] AS Option10, --SUM([Value]) where OptionID = 10 Grouped By ID
[11] AS Option11, --SUM([Value]) where OptionID = 11 Grouped By ID
[12] AS Option12, --SUM([Value]) where OptionID = 12 Grouped By ID
[13] AS Option13, --SUM([Value]) where OptionID = 13 Grouped By ID
[14] AS Option14, --SUM([Value]) where OptionID = 14 Grouped By ID
[15] AS Option15, --SUM([Value]) where OptionID = 15 Grouped By ID
[16] AS Option16 --SUM([Value]) where OptionID = 16 Grouped By ID
FROM
(
SELECT ID, OptionID, [Value]
FROM @Table
) UP
PIVOT (
SUM([Value])
FOR [OptionID] IN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16]) --Values held in the OptionID column
) AS pvt
ORDER BY ID
This give you will still have a lotstrong blend of hits to this table every time you need to read from it.:
- Easy of adding new options (all this code can be loaded up in a
VIEW
orCTE
for easy of use. When a newOption
is added you would simply add an additional Column in the final select[17] AS Option17
and Add, [17]
in theFOR [OptionID](...)
piece of thePIVOT
). - Space savings since records can only be added if they differ than the default and that can be applied via a
CASE
statement. EAV
tables are really narrow, so as long as the table is indexed well performance should be really good.
That is probably one of the harder ones to support when it comes to new options coming. It will also require the most amount of work in your initial development. But a VIEW
can probably be used to get around most of that. However I think space and performance wise it will give you the besta lot of both worldsbenefits, which should be the primary priority when it comes to a database, particularly if you are already in a 2 Million row world with this one table.