September 1, 2014 at 6:46 pm
We have a small module comprised of a main parent table and about 25 lookup (child) tables. The main table has all the 25 reference key id (some null) and there is plan from the business to add another 25+ child tables/keys. The business requirements keep us guessing from one week to the next and they said in a few months there could be 50+, who knows. Is this traditional star schema a good approach?
Main table(id,code,name,description,fk1,...f25)
Child table(id,code,name,description) x25
Is it a good idea to denormalise these lookup tables and put into one global lookup table with a type?
new global Child table(id,type,code,name,description)
That way they can add any number of lookup types to play with, but performance would be very bad?
The application developers are frustrated every time they have to keep adding lookup tables.
How do other developers deal with a never ending story like this?
September 1, 2014 at 8:28 pm
Before trying to build a data warehouse, I would definitely recommend reading Ralph Kimball's book. I have a really old copy of The Data Warehouse Toolkit. There are newer versions, but the thing to take away from the book is what a properly structured star schema looks like. Once you have that, you can go on and build cubes etc and your structure won't really change. Yes, you could add more dimension (lookup) tables... But I would start by getting a solid understanding of the star schema and how to implement it right.
September 1, 2014 at 9:05 pm
yes I know its a star-schema, but its not for the purpose of a warehouse/cube solution. This is a day to day system and the only design change I can think of for them to full fill an unlimited number of lookup items requests is to have Main table(id,code,name,description,<xml containing any number of child lookup data>), has anybody done something like this before?
September 1, 2014 at 9:53 pm
skeezwiz (9/1/2014)
yes I know its a star-schema, but its not for the purpose of a warehouse/cube solution. This is a day to day system and the only design change I can think of for them to full fill an unlimited number of lookup items requests is to have Main table(id,code,name,description,<xml containing any number of child lookup data>), has anybody done something like this before?
Quick thought, although a generalized lookup table might indeed cover many of the tables and be more flexible in terms of adding new "types", the benefits are quickly outweighed by the penalties on the processing side of things.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply