Advice on design - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Advice on design

Author

Topic

cidr2
Starting Member

28 Posts

Posted - 2009-02-20 : 09:05:03

The database I'm working on should have a table that's similar to the one below (tblCosts). I would like to ask for some advice.

In tblCosts below there are three columns and the grade Column values will never change (there will always be a Director etc)

Because the other column values can change like the amounts£, I was thinking of making a second table that held the Grade column values and call that lookup table "Grades". I would then replace the Grade column in tblCosts as an ID field that references tblGrade This is so when the "Per Hour" and "per Day" values change the rows can be updates with the new amounts, so the row with Director shows different amounts.

Here's the thing, I was thinking creating a table a different way and have the Grade values as the columns and have "Per Hour" and "Per Day as the the values in the table. I'd still have a lookup table, only this time it would only have two values "per hour" and "per Day".

The reason I was thinking this was because when the amounts change there will be less rows created and if someone was manually inputting the amounts, there's only two new rows in tblCosts; "Per hour" and "Per Day".

I hope that makes sense. I seem to have a skill at confusing myself and would appreciate if someone could help.

Again, thanks for all help in advance.

Cheers

tblCosts
Grade / Per Hour/ Per Day
Director / £100 /£1,000
Assistant Director/ £100/ £900
Portfolio Manager/ £80/ £600
Project Manager / £60 £400/
Data Info Support/ £40/ £300
Performance Auditor/ £40/ £300
Project Officer / £30 £200/
Admin Support / £20 £220/

Alternative Design for tblCosts
Grades / Director/ AssistantDir / PortfilioMan / etc etc

PerHour /....£100/...... £100/........../£80
PerDay /....£1,000/..... £900.........../£600

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-20 : 11:08:37

You could use an extra column(s) to show when data is valid from /to. This way you would have a historical record of changes rather than only seeing current values. E.g.
tblCosts
Grade / Per Hour/ Per Day / ValidFrom/ ExpireDate

cidr2
Starting Member

28 Posts

Posted - 2009-02-21 : 08:19:02

Thanks for replying darkdusky!

I think that's a good idea and I'm considering it.

I take it that you prefer the first table design rather than the second? First design being a table with the Per Hour and Per Day as columns and not values?

Please let me know:)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources