master detail problem [message #682560] |
Wed, 28 October 2020 17:12 |
|
compuscience
Messages: 97 Registered: September 2012
|
Member |
|
|
i create master table emp(emp_number,emp_name,id,salary)
with detail 1 dept(dept_name,emp_number,id)
with detail 2 job(job_name,emp_number,id)
i didn't make primay key and forign key i make relation between them with emp_number,id
the only problem when i make update in master block not affect in detail 1 and detail 2 block
what can i do???
|
|
|
|
|
|
|
|
|
|
|
Re: master detail problem [message #682586 is a reply to message #682582] |
Fri, 30 October 2020 04:00 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You have been caught out by the problem of using a natural key as a primary key. Bad move! Your primary/foreign key relationships need to be based on surrogate keys, not natural keys. You should have studied the use of surrogate keys and natural keys in college (it is a standard topic that all data science courses should cover) but if not better start now. This article is fair start,
https://en.wikipedia.org/wiki/Surrogate_key
and the papers published by Ralph Kimball take the topic further. Look up Slowly Changing Dimensions, too: They are a problem caused by use of natural keys that has made many multi-terabyte multi-million dollar databases worthless.
|
|
|
|