CODEWITHSUNDEEP
sqlsql-servert-sqlprimary-key
mko
mko

Reputation: 7325

Update table by two different users

I have a situation where stock quantity can be updated both from administrator and supplier, each containing two different keys adminID and supplierID.

What would be the best way to keep track of who made the update

ArticleID   Quant   DateModified   UpdatedBy  AdminID   SupplierID
-------------------------------------------------------------------
10493         -1     2011/03/18      0-23        0          23
10495         -5     2011/03/18      5-0         5          0

I was thinking of using either a) column UpdatedBy or b) (columns adminID and supplierID). For a) I would not be able to check primary key, and for b) I would have to either create 0 for adminid and supplierid, or accept null values.

I would appreciate your comments.

Upvotes: 0

Views: 131

Answers (2)

Filip De Vos
Filip De Vos

Reputation: 11908

You only want 1 column UpdatedBy. I would make the schema

Article(ArticleID, Quant, DateModified, UpdatedBy references User(UserId))
User(UserId, UserType references UserType(Type), UserName)
UserType(Type, TypeName, Description)

An update can only be done by one user at a time, so there is no point creating 2 fields. If you want to keep track of "History" it is possible to add an IsActive field which indicates the current version of the data.

Article(ArticleID, Quant, DateModified, IsActive, UpdatedBy references User(UserId))

If for supplier information you have a lot of properties which are not relevant for administrators you can make a SupplierInfo table with a foreign key to the User table. 

SupplierInfo(UserId references User(UserId), FirstName, LastName, CompanyName, Address)

Some other things to consider are if you want to have different users for 1 supplier. To do this you changes the SupplierInfo table a bit make a mapping table between User and SupplierInfo.

SupplierInfo(SupplierId, FirstName, LastName, CompanyName, Address)
SupplierContacts(SupplierId references SupplierInfo(SupplierId), UserID references User(UserId))

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

I would choose two fields, AdminID and SupplierID with FK relation to Admin table and Supplier table.

I would also accept null values in AdminID and SupplierID because otherwise you would need to have one "dummy" Supplier row and one "dummy" Admin row to be able to have the FK in place.

To enforce that either AdminID or SupplierID is specified you can create a check constraint.

(AdminID is not null or SupplierID is not null)

Upvotes: 2

Related Questions