Database Design: Explain This Scheme

Question

Full disclosure ... I am trying feverishly here to find out more about databases, so I put in at that time, and also tried to get this answer from the source to no avail.

Barry Williams from the database has such a scheme.

Customer and Fees Scheme

I am trying to understand the partitioning of address tables in this diagram. It is clear to me that the Addresses table contains detailed information about this address. The Client_Addresses and Staff_Addresses tables are what bothers me.

1) I understand the use of primary foreign keys as shown, but I was on the assumption that when they are used, you do not have a resident primary key in the same table (date_address_from in this case). Can someone explain the reasoning to both and put into words how this works?

2) Why did you use date_address_from as your primary key instead of something like client_address_id like PK? What if someone enters two addresses in one day, will there be conflicts in his design? If yes or no, what?

3) In accordance with normalization standards ... Since the values ​​of date_address_from and date_address_to are the same in the Client_Addresses and Staff_Addresses tables, should these fields just not be included in the main address table?

Answer 1

Rating

Perform an audit first and then specific answers.

• . . , , , , . , . , - .

• ; (. , ). other_details eg.s . : StreetNo, ApartmentNo, StreetName, StreetType .. line_1_number_street, .

  • Person .

  • , , , .

• , - . . , -. 35 .

• () 500 "" . , "", , () () () , ; .

  • "",

  • 500 .

  • . .

• , - . , , . , ; . , : .

• , : ; ; ; .., , .

, "". "", . , , , "". , - , .

• , . PK client_addresses client_id, address_id, date_address_from). , , .

  • , , , "" ; other_details , , .

  , " " . , PK . ; .

• , .

  , , ; , .

• .

- .

Answer 2

1) , : (staff_id, address_id, date_address_from) (client_id, address_id, date_address_from). , -, , / , , .

2) "id" . . ? , , .

3) . , / - , .

Answer 3

3) ... date_address_from date_address_to Client_Addresses Staff_Addresses, ?

. .

, - . " ", , .

, ? . , .

, - . ? ? , : " " "?... , ".

, ​​ . . "" ( , ) , , . "" . - .

, ?

Answer 4

.

, , (b/c client id = 1, = 1 ).

, 1 , . , , , .

2 - pk , clientID, AddressID, . , - 6 , 6 , , - 2 3 client_address.

from_Date , , , .

3 - ( 2).

Answer 5

, :

1) PF , .

2) Staff_Addresses - {staff_id, address_id, date_adderess_from} date_adderess_from

3) , 2)

Answer 6

Staff_Addresses date_address_from staff_id/address_id . DBA, , / . , , , Staff_Address_Id PK staff_id/address_id/date_address_from.

As for your last problem, the address table is indeed the general structure for storing addresses. He should not care about the date ranges during which someone lived there. This is best left to specific implementations of an address, such as customer / employee addresses.

Hope this helps a bit.