Look Up Tables - Proper Order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Look Up Tables - Proper Order

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-23 : 23:17:54

I have data that I need to store in look up tables. I've tried a couple of combinations of tables and keep getting stumped with the best way to store them because I keep feeling like I'm duplicating, but can make an argument for each way I choose to store. So I was hoping to get some feedback from folks here.

The business problem is that I have valid combinations that come from a production database. The combinations themselves have descriptions. The descriptions then have valid date ranges and a status associated with them during that date range. This is what I have now (writing as declared tables and shortened for ease of use):


declare @PassClassCombo table (PCCId int, Tnum int, Ttype int)
insert into @PassClassCombo (PCCId, Tnum, Ttype)
values
(1, 2, 119),
(2, 2, 143),
(3, 2, 144),
(4, 7, 118),
(5, 8, 119)

declare @PassClassStatus table (PCSId int, PCCid int, PassClassDescription varchar(20), FromDate date, ToDate date, IsActive tinyint)
insert into @PassClassStatus (PCSId, PCCid, PassClassDescription, FromDate, ToDate, IsActive)
values
(1, 1, 'RegularFare', '2011-01-01', '2011-06-30', 0),
(2, 1, 'NonFare', '2011-07-01', null, 1),
(3, 2, 'SchoolFare', '2011-01-01', '2011-06-30', 0),
(4, 2, 'University', '2011-07-01', null, 1),
(5, 3, 'PSD', '2011-01-01', null, 1),
(6, 4, 'CSU', '2011-01-01', null, 1),
(7, 5, 'MTP', '2011-01-01', '2011-08-30', 0)


declare @checkdate date
set @checkdate='2012-01-01'

select *
from @PassClassCombo PCC
join @PassClassStatus PCS on PCS.PCCid=PCC.PCCId

where PCS.IsActive=1 and PCS.FromDate<=@checkdate and (PCS.ToDate>=@checkdate or PCS.ToDate is null)

The results are as expected; the issue is whether or not I'm storing this properly. The data will be read-only as this is for reporting purposes only.

The first table, @PassClassCombo is used primarily during an insert query to ensure that only valid combinations are taken from production and loaded into a small archive or data warehouse that I'm developing. But since there are no descriptions in the table, once I need to get a look up on the PCCId from other tables, it's just easier to go directly to the second table (@PassClassStatus). So I'm wondering if I really need to separate them out, or maybe move the description to the first table.

Thanks folks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-23 : 23:22:56

Nope. How can you move description to first table? As I see description is changing attribute based on date range so you cant separate it out i guess

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-23 : 23:29:34

Ok, maybe I'm just second guessing myself then. I've had it this way for about a week and I just keep wondering about the fact that I skip over the first table when joining to another table:;
Declare @Ridership table (RSId int, PCCId int, SDate date)
insert into @Ridership (RSId, PCCId, SDate)
Values
(1, 2, '2011-08-01'),
(2, 2, '2011-08-01'),
(3, 2, '2011-08-01'),
(4, 4, '2011-08-01')

Select *
from @PassClassCombo PCC
join @PassClassStatus PCS on PCS.PCCid=PCC.PCCId
join @Ridership R on R.PCCId=PCC.PCCId

where PCS.IsActive=1 and PCS.FromDate<=R.Sdate and (PCS.ToDate>=R.Sdate or PCS.ToDate is null)

In the above select statement, there's really no reason to go the @PassClassCombo table, even though the PCCId got into the @Ridership table via an insert query using the @PassClassCombo table.

But I appreciate your insights Vis!

Thanks

(Changed @Checkdate to Sdate in last Select)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources