Designing database - 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

Designing database

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-03-22 : 10:53:19

just 2 questions:

1. Should most tables have these fields?
'created date'
'created by'
'modified date'
'modified by'

2. A person has an address and a billing address.
a)Should I use 1 address table. If the addresses are the same then no point entering them twice therefore i should have another bool column if billing is the same.
b) Can this address table be used for clients or suppliers address?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2011-03-22 : 11:30:09

1. Should most tables have these fields?
'created date'
'created by'
'modified date'
'modified by'

Your questions all depend on whats required, but I do tend to put CreatedOn and ModifiedOn in tables just so you can find what was insert/modified on specific days

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-22 : 12:28:18

in addition to NeilG response,

if your database is exposed to a single user then perhaps the columns (created/modified date and by) might not have that much importance as in the case of multi-User. In most cases these columns are introduced for Data Analysis purposes e.g. how the data has been inserted, who did it, when this happened.

Cheers
MIK

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 12:48:45

No, they should have these fields


	[ADD_ID] [char](12) NOT NULL,
	[ADD_TS] [datetime] NOT NULL,
	[UPDATE_ID] [char](12) NOT NULL,
	[UPDATE_TS] [datetime] NOT NULL

;-)

If the table is NOT updateable, like as say from a feed, then there is no need.

1 Address table with a type column

Primary
Billing
Both
Girlfriend
...

;-)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 12:49:48

and if you want to get nuts...a history table identical to the base table, and a trigger to move a modification to history

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-03-22 : 12:58:47

quote:
Originally posted by X002548

No, they should have these fields

[ADD_ID] [char](12) NOT NULL,
[ADD_TS] [datetime] NOT NULL,
[UPDATE_ID] [char](12) NOT NULL,
[UPDATE_TS] [datetime] NOT NULL

;-)

If the table is NOT updateable, like as say from a feed, then there is no need.

1 Address table with a type column

Primary
Billing
Both
Girlfriend
...

;-)

regarding the address table, is this what you mean?
ID,
address1
address2
address3
postcode
country
addressType (reference an address type table)
both (yes/no)

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 13:16:19

Sort of...

If you already had a ADDR_TYPE Column, I might add a value of Both...but that would be bad data modeling I think....

The Both Column as an Indicator I guess would work, (I might Call it Billing_Ind), but again...bad data modeling

But it's a physical implementation issue...to save 2x the number of rows...so I would go with the indicator or the value "Both", or "Primary and Billing" as value in type

OK....

Please don't hurt me Joe

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

insanepaul
Posting Yak Master

178 Posts

Posted - 2011-03-24 : 05:21:14

quote:
Originally posted by X002548

Sort of...

If you already had a ADDR_TYPE Column, I might add a value of Both...but that would be bad data modeling I think....

The Both Column as an Indicator I guess would work, (I might Call it Billing_Ind), but again...bad data modeling

But it's a physical implementation issue...to save 2x the number of rows...so I would go with the indicator or the value "Both", or "Primary and Billing" as value in type

OK....

Please don't hurt me Joe

Thanks, good to hear other persons views

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources