Looking the best practice to design table

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)

Looking the best practice to design table

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-26 : 02:35:58

Let's say below is my table to stored Login Information.

create table tUsrAcct
(
trnxid int identity(1,1) primary key clustered,
usrid varchar(100) not null constraint tUsrAcct_usrid unique,
pwd varchar(100) not null
)

My question is,
1. What the best practice to store Created By, Created Date, Updated By, and Updated Date?

Hope somebody can consult

Sachin.Nand

2937 Posts

Posted - 2010-02-26 : 02:58:08

Create the column names as Created By, Created Date, Updated By, and Updated Date in the same table & insert the values from insert sp.

PBUH

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 03:42:51

I have the same 5 columns at the start of every table:

EditNo - incremented on each edit, used to check record has not changed
CreateDate - create date, set by Default
CreateUser - set by Insert SProc
UpdateDate - Update date, set by default, changed by Update SProc (NOT changed by Trigger - in case want to do bulk import from another source and retain UpdateDate from that other source)
UpdateUser - set by Insert SProc

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-26 : 03:49:28

quote:
Originally posted by Kristen

I have the same 5 columns at the start of every table:

EditNo - incremented on each edit, used to check record has not changed
CreateDate - create date, set by Default
CreateUser - set by Insert SProc
UpdateDate - Update date, set by default, changed by Update SProc (NOT changed by Trigger - in case want to do bulk import from another source and retain UpdateDate from that other source)
UpdateUser - set by Insert SProc

may i see your create table statement?

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 03:59:17

[code]
CREATE TABLE dbo.AAA_BBB_CCC_Table
(
bbb_ccc_zEditNo smallint NOT NULL,
bbb_ccc_zCrDt datetime NOT NULL,
bbb_ccc_zCrUser varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
bbb_ccc_zUpDt datetime NOT NULL,
bbb_ccc_zUpUser varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
bbb_ccc_ID int NOT NULL IDENTITY (10000, 1),
...
) ON [PRIMARY]
GO

DECLARE @v sql_variant
SET @v = N'MyTable Description'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', NULL, NULL
GO
DECLARE @v sql_variant
SET @v = N'Edit No'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', N'column', N'bbb_ccc_zEditNo'
GO
DECLARE @v sql_variant
SET @v = N'Create Date'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', N'column', N'bbb_ccc_zCrDt'
GO
DECLARE @v sql_variant
SET @v = N'Create User'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', N'column', N'bbb_ccc_zCrUser'
GO
DECLARE @v sql_variant
SET @v = N'Uupdate Date'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', N'column', N'bbb_ccc_zUpDt'
GO
DECLARE @v sql_variant
SET @v = N'Update User'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', N'column', N'bbb_ccc_zUpUser'
GO
DECLARE @v sql_variant
SET @v = N'MyTable ID'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'AAA_BBB_CCC_Table', N'column', N'bbb_ccc_ID'
GO

ALTER TABLE dbo.AAA_BBB_CCC_Table ADD CONSTRAINT
DF_AAA_BBB_CCC_zEditNo DEFAULT (1) FOR bbb_ccc_zEditNo
GO
ALTER TABLE dbo.AAA_BBB_CCC_Table ADD CONSTRAINT
DF_AAA_BBB_CCC_zCrDt DEFAULT (getdate()) FOR bbb_ccc_zCrDt
GO
ALTER TABLE dbo.AAA_BBB_CCC_Table ADD CONSTRAINT
DF_AAA_BBB_CCC_zUpDt DEFAULT (getdate()) FOR bbb_ccc_zUpDt
GO

ALTER TABLE dbo.AAA_BBB_CCC_Table ADD CONSTRAINT
PK_AAA_BBB_CCC_Table PRIMARY KEY CLUSTERED
(
bbb_ccc_ID
) ON [PRIMARY]
GO
[/code]

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-26 : 04:31:31

tq sir

Jaime
Starting Member

19 Posts

Posted - 2010-02-26 : 14:49:04

quote:
Originally posted by Kristen

I have the same 5 columns at the start of every table:

EditNo - incremented on each edit, used to check record has not changed
CreateDate - create date, set by Default
CreateUser - set by Insert SProc
UpdateDate - Update date, set by default, changed by Update SProc (NOT changed by Trigger - in case want to do bulk import from another source and retain UpdateDate from that other source)
UpdateUser - set by Insert SProc

Why not use a timestamp or rowversion column in place of EditNo? If you use a timestamp/rowversion column, the server will take care of maintaining it for you. Also, many tools recognize timestamp and/or rowversion columns and automatically create optimistic locking behaviors.

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 15:38:54

If I had my time over I would almost certainly consider TIMESTAMP.

I think this has come up before and I had a credible reason for preferring INT at the time, but I can't remember it now.

EditNo does allow us to import data from another table WITHOUT the EditNo changing (don't think you can do that with TIMESTAMP), but that's a pretty skinny justification for not using it!)

EditNo is INT - which is 4 bytes (in fact Small Int would do, only 2 bytes), whereas RowVersion is 8 bytes. Again, only a minor point.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources