[thelist] Tables referencing each other

[Luther, Ron]

Bill Moseley asked a table structure question:


>>I have a customer table.  Each customer has a profile.
>>When a customer's profile is updated I insert a *new* row in the
>>profile table instead of updating an existing one.
>>
>>This is done because there's also an "orders" table and each order is
>>associated with the profile that was current at the time the order
>>was created.  (Hence, "current_profile" in the cust table.)



Hi Bill,


How about:

*customer table*
-- Cust_ID
-- <numerous other fields>

*profile table*
-- Profile_ID
-- Cust_ID
-- Profile_Creation_Date (or Timestamp if y'all create more'n one a day)
-- <even more silly fields>

*orders table*
-- Order_No
-- Stuff_What_Was_Ordered
-- Qty
-- <more junk>
-- Profile_ID


>From the profile table you could derive a new *current profile table* by pulling the unique max creation date record per cust_id ... or (if you prefer) by copying all of the records from the profile table and adding a new 'Current_Profile_ID' field at the end to house the id corresponding to that max create date value.

(I like 'simple'.  Storage is cheap.  An extra table like this is easy for me to visualize and see how to join.  Yeah, you could do this same thing with some correlated subquery sql-fu ... and it would be cooler and probably even more efficient ... but this is simpler for me to visualize.)


I don't know exactly where you are headed, but this kind of structure should allow you to do quite a bit.


Good Luck,
RonL.