Geek Answers Handbook

Database: insert new rows or update existing ones?

Object-oriented design encourages the use of immutable objects to improve thread safety and performance. I am wondering if this relocates relational databases.

Am I better off updating existing rows or inserting new ones that act as overrides?

  • Register Usage
    • Each employee is associated with only one company.
    • Employees change their company over time.
    • The name of the employee must be unique.
  • Schema
    • Employee [name, company]

Option 1 . Each time an employee changes companies, insert a new line Employee [name, company]. The application is prompted to skip older lines (which contract in the background thread over time). Option 2 . Every time an employee changes companies, update the existing line.

Option 1 reminds me of immutable objects in that it is thread safe (no locks needed). On the other hand, every time an employee changes companies, I have to clone all the related objects and point them to a new record. In addition, it is unclear how to prevent duplication of employees created by mistake.

2 , READ_COMMITTED.

+5
5

, . , () .

( Integer, , ..). , . , , , . , .

, , - , .

UPDATE: marc_s , . . . :

  • (.. ).
  • , / .

.

+4

:

  • , , "" , "ValidTo", , ""

  • , , .

, , : , . " " ? , 1 ( " " - , ) . , .

+1

, Data Warehousing " ". , , , .

: - 1 , . , , , - . "" .

OLTP , , . . , , . , ​​ , , , .

+1

. , . , . , .

. , . , ,

Name    Company
--
Gili    Microsoft
Marc    Oracle


Person named "Gili" is currently an employee of company "Microsoft".
Person named "Marc" is currently an employee of company "Oracle".

, . ( .)

Person named "Gili" once was an employee of company "Microsoft".
Person named "Marc" once was an employee of company "Oracle".


Person named "Gili" once was an employee of company "Oracle".

, . ( , -, .)

, , ,

Person named "Gili" is currently an employee of company "Microsoft".
Person named "Marc" once was an employee of company "Oracle".

. .

,

Person named NAME is currently an employee of company COMPANY.

, .

Person named NAME once was an employee of company COMPANY.

, .

0
tblPerson
    PersonID 
    LastName
    FirstName

tblCompany
    CompanyID
    CompanyName

tblCompany_Employee
    PersonID
    CompanyID
    StartDate
    EndDate

tblCompany_Employee. , , . EndDate UPDATEd NULL empoyments.

, SELECT PersonID FROM tblCompanyEmployee WHERE EndDAte IS Null.

:

SELECT PersonID
FROM tblCompany_Employee
WHERE PersonID = @PersonID
AND CompanyID = @CompanyID
AND EndDate IS Null

. , , , true EmployeeID. , , false.

In this case, the audit trail is saved, and it is possible (with some refinement, obviously - I'm pretty rude here) to determine:

and. Employment history for a person in all companies B. All persons employed by a particular company C. Who currently works for a particular company D. Who currently does not work for a specific company D. Etc.

No data loss due to UPDATES that overwrite the historical record.

0
source

More articles:


All Articles