one to many on a single table - 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

Database Design and Application Architecture

one to many on a single table

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 02:24:34

DB gurus !!
Is it a poor DB design if I have a recursive table ?
(One to many relationship on a single table)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 02:27:25

do you mean relationship like employee & manager? can you elaborate?

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 02:30:37

Yes, exactly like employee & manager.
I came across that example as well.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 02:34:50

quote:
Originally posted by u2envy1

Yes, exactly like employee & manager.
I came across that example as well.

no problem in doing that. If using SQL 2005 or later you can use recursive CTE to retrieve them also

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 02:39:12

Recursive CTE ?
Googled it & shows Common Table Expressions.
Will read up more about it. Thx visakh !!!
We had a consulting guy come in & check our DB design.
The consultant that claims to be a senior DB guy.
Said that any recursive table in a DB is a poor design.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 02:50:22

quote:
Originally posted by u2envy1

Recursive CTE ?
Googled it & shows Common Table Expressions.
Will read up more about it. Thx visakh !!!
We had a consulting guy come in & check our DB design.
The consultant that claims to be a senior DB guy.
Said that any recursive table in a DB is a poor design.

what is your exact scenario? can you give a jist on that?

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 03:20:52

We busy with a time & attendance management.
Table Name TimeCategories.
Fields
TCat_ID
TCat_Name
TCat_Rate
TCat_Normal
TCat_ReferToNormal
TCat_AlternateCategory

This table works out if the time you clocked in will it be
normal time. Meaning normal hours worked or holidays, sundays. Which
all have a time & a half calculation or double pay.

Example data:

0 Normal Time 1.0 True False NULL
1 Overtime 1.3 1.3 False False NULL
2 Overtime 1.5 1.5 False False NULL
3 Overtime 2.0 2.0 False False NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 03:23:15

ok. thats fine. but where does recursive relation come here?

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 03:24:49

Besides this example.
The DB guy only saw the DB diagram & never see the fields or relationship & just pointed out that a recursive table is a poor DB design. Im doubting that this consulting company has the skills.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2008-10-06 : 03:32:45

Sorry it has a relationship on the TCat_ID & TCat_AlternateCategory.
An employee can get paid normal pay & for overtime on the same day if worked late.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:02:03

quote:
Originally posted by u2envy1

Sorry it has a relationship on the TCat_ID & TCat_AlternateCategory.
An employee can get paid normal pay & for overtime on the same day if worked late.

ok even that relationship is not recursive. its just one to many. you'll have a shifttype for normal and overtime and then relate pay record to shifttype to decide if its for normal or overtime. where does recursion come here? Am i missing something?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources