Should -Can- a parent be it's own child?

[Scott Axton]

Hi every one! I'm back to the well of wisdom with another question. I hope someone can shed a little light for me.

It is my understanding, in a relational DB, that one of the main rules for normalization is to eliminate repetitive information. Another advantage of a relational DB is to avoid maintaining multiple tables with the same data through the use of child tables and relationships.

Here is my problem and question:

I am creating an order / billing application for a small manufacturing business.

I need to have the ability for some of my customers to
have the possibility of multiple ship to addresses.

I first set my customer table up with this idea
Cust1 ABC Corp Bill_to: Same
Cust2 XYZ Corp store #1 Bill_to: XYZ Corp
Cust3 XYZ Corp store #2 Bill_to: XYZ Corp
Cust4 XYZ Corp store #3 Bill_to: XYZ Corp
and so on.


Then I spotted the repeating info an thought of a one-to-many table.
For instance XYZ is the main customer and the billing would all go to:
XYZ Corp
123 Main
This City USA

But I can ship to:
XYZ Corp Store in Main
XYZ Corp Store in California,
XYZ Corp Store in Colorado........... and so on.

This eliminates the problem of repeating BUT doesn't this also introduce the need to maintain to sets of data when I have customers that the ship_to and bill_to are the same?

So now my question: Can I (Would I want to?)link a partent table back on itself as a child?

Thoughts on how I can handle this would be GREATLY appreciated.

Scott

[Ohlen Cartmell]

RE: Should -Can- a parent be it's own child?

FWIW

I'd put all the addresses in the same table with the same company name (repeated each time) and all the addresses. In the invoice header I'd lookup the billing address and then lookup the shipping address.

Another approach would be to put the company name in one table and link one to many to all the addresses. Lookup hte company name in the first table and lookup the billing and shipping addresses in the second table using a filtered lookup.

Others probably have more and better ideas.

[Daniel Weiss]

RE: Should -Can- a parent be it's own child?

Scott
I have the same issue where I have a customer linked to many shipping address by a one to many set.
Keep in mind in your invoice header table you don�t need to have the Bill To address fields because you probably have a set for your order entry which includes the customer table as a 1 to 1 and is linked by the customer id field, but you do need to have fields to enter the shipping address so it can be changed for each address, now you could one field the �Ship To Name� which you can create in fields rules as a lookup to the shipping address table you need to add a filter something like �invoice_header->customer_id = customer_id� so only the address belonging to that customer will show up in the lookup.
If you want a neater look put the shipping fields on the form and hide them and put an RTF field with the full shipping address now create button on the form to call an Xdialog box to enter the address
See attached picture
Here is an example which you will need to modify with field names

CHANGE_SHIP_ADDRESS
DIM v_sphone AS c
v_sphone = topparent:Sphone.value
DIM v_scontact AS c
v_scontact = topparent:Scontact.value
DIM v_scountry AS c
v_scountry = topparent:Scountry.value
DIM v_szip AS c
v_szip = topparent:Szip.value
DIM v_sstate AS c
v_sstate = topparent:Sstate.value
DIM v_scity AS c
v_scity = topparent:Scity.value
DIM v_sadd3 AS c
v_sadd3 = topparent:Sadd3.value
DIM v_sadd2 AS c
v_sadd2 = topparent:Sadd2.value
DIM v_sadd1 AS c
v_sadd1 = topparent:Sadd1.value
DIM v_stoname AS c
v_stoname = topparent:Stoname.value

DIM get_address as C
DIM get_address_rl_def as C
get_address_rl_def = "k=ship_add,{keylist_build(\"H=.025,1:60[Name],2:60[Address]\",''+recno(),left(''+Stoname,60),left(''+alltrim(Sadd1)+\" \"+alltrim(Sadd2)+\" \"+alltrim(Sadd3)+\" \"+alltrim(Scity),60))}{Cust_id = "+quote(cusid)+"}"
dim rec[1] as p
dim addr_get as L
addr_get = .t.
table_name = a5.Get_Path()+chr(92)+"ship_add.dbf"
rec.initialize_from_table(table_name,"recno()=1")
if eval_valid("rec[1].stoname") =.f.
addr_get = .f.
end if

DIM new_ship_addr as C
new_ship_addr = ui_dlg_box("Shipping Address",
;
{endregion};
%dlg%,1
Rec.clear()
rec.initialize_from_table(table_name,"recno()="+get_address)
if eval_valid("rec[1].stoname") = .t.
v_sphone = rec[1].Sphone
v_scontact = rec[1].contact
v_scountry = rec[1].Scountry
v_szip = rec[1].Szip
v_sstate = rec[1].Sstate
v_scity = rec[1].Scity
v_sadd3 = rec[1].Sadd3
v_sadd2 = rec[1].Sadd2
v_sadd1 = rec[1].Sadd1
v_stoname = rec[1].Stoname
end if
end if
a_dlg_button = ""
else if a_dlg_button = "Cancel_button_click" .or. a_dlg_button = "save_button_click" then
' close
else
a_dlg_button = ""
end if


%code%)

if new_ship_addr = "save_button_click"
topparent.restrict_change = .f.
topparent:Sphone.value = v_sphone
topparent:scontact.value = v_scontact
topparent:Scountry.value = v_scountry
topparent:Szip.value = v_szip
topparent:Sstate.value = v_sstate
topparent:Scity.value = v_scity
topparent:Sadd3.value = v_sadd3
topparent:Sadd2.value = v_sadd2
topparent:Sadd1.value = v_sadd1
topparent:Stoname.value = v_stoname
end if

Daniel Weiss

[Blake]

RE: Should -Can- a parent be it's own child?

First off, yes, there are many situation where a parent can and should link to itself. In a contact database, for example, you might want to store relationships between people. Spouse, for example.

You wouldn't have one table for husbands and one for wives. (And even if you did, mightn't THAT become an issue in this day of domestic partnership.) No, the sensible, flexible thing to do is to a table back to itself. Often this is done indirectly through intermediate tables. So, a contact record might link to an intermediate table that contains not just a link to other contact records, but also a description of the relationship, like spouse, parent, child, doctor, whatever.

However, you don't really have a situation like that. :-) You want to store addresses, and a person might have more than one. One-to-many, straight up. The reason it seems complicated, perhaps, is that you're still keeping some address info in your base table. Don't. Put all addresses into a separate table. Right now you just have "ship to" and "bill to", but who knows? You might want to expand to using multiple ship to addresses (like with gift sending).

===Blake===

[Scott Axton]

RE: Should -Can- a parent be it's own child?

Hey guys -

Thanks for the responses. Sorry I can't reply properly right now. I came in to a crashed network this AM. Looks like lightning... GRRRR! Tuesdays after a Monday holiday - Don't you love them??

Will be back soon.

Scott