How to work with m:n relationships?
Date: 19 Feb 2006 10:48:00 -0800
Message-ID: <1140374880.419801.155310_at_z14g2000cwz.googlegroups.com>
I will use the post which was on comp.database on 25 January 2006, “Design Query”, as one example for m:n relationships. Here we should to design a database to track product subscriptions. The solutions are simplified so that we can concentrate on m:n relationship and on some dis/adventages. Let following solution be as it is usually done in the relational databases.
Subscriber ( ssn, name, address, phone, email, login, password, datefrom, dateto, other-details)
Product ( product_code, name, price, other_details) SubscriberProduct ( ssn, product_code, type_of_subsription, date_from, date_to )
Keys are ssn, product_code and (ssn, product_code) respectively. I will compare above solution with the following (which I defined in www.dbdesign.com):
SubscriberSt (Key1, ssn, name, address, phone, email, login, password,
datefrom, dateto, other_details)
ProductSt (Key2, product_code, name, price, date_from, date_to,
other_details)
SubscriberProductSt ( Key3, Key1, Key2, type_of_subsription, date_from,
date_to )
Here every Key is one column generated key. This solution has suffix
St and I will call it St-solution. So, Keys are only difference between
SubscriberSt2 = (k12, s1, n1, a1, p1, e2, l1, ps1, df2, dt2)
(St-solution can support this change)
Now we can ask ourselves, what will be if any other attribute (for
example email) change its value at same df2 time and has dateto not =
dt2. Subscriber2 will get the duplicate key, while SubscriberSt2 will
work normally.
First solution uses time, as a key’s attribute, compound key etc. Is
there an explanation which will say when to use compound key or when to
use surrogate key. What are the meanings of these keys, etc?
Can we say that time is property of an entity? And related with this,
we can set another question. What does it mean that two entities are
identical? Is it means that they are one same thing or they are the two
things perfectly similar? In the second case their entity’s
properties are same but their system’s properties can be different
(like time/space/position properties). Here my main objection is
regarding construction of a key. Can we use principle: if for n
attribute’s values, two rows are same, then I can always find n+1
attributes so that rows are different? Can I say that for any n
attributes, I can always set two rows to be with same values?
2.
Now let St-solution be:
SubscriberSt1 = (k11, s1, n1, a1, p1, e1, l1, ps1, df1, dt1)
SubscriberSt2 = (k12, s1, n1, a1, p1, e2, l1, ps1, df2, dt2)
ProductSt1 = (k21, p_c1, name1, price1, df3, dt3)
SubscriberProductSt1 = (k31, k11, k21, type_of_sub1, df4, dt4)
Here as addition to the referential integrity, it should be added that
PK-FKs should
correspond to each other by their states.
3.
Microsoft ADO.NET has possibility to work with the disconnected
objects. We can use these objects to connect to a database, now we can
get a data from the database, set them in the disconnected objects and
then we can disconnect from the database. When we are disconnected we
can update these data, for example we can update a key. Now we can
connect to the database again and update it. Problems arise when there
are the two users which are working in disconnected mode with the same
row and one user updates the key, but the other one needs this key.
There is Microsoft’s solution for these kinds of problems but it can
be very complicated and there are many combinations. The St-solution
which is shown above (in part 1 and 2) can handle these problems with
ADO.NET easy. In fact St-solution doesn’t have updates at all. The
difference is that ADO.NET solution is based on programming theory.
St-solution is based on DB theory.
ADO.NET is great step, but maybe DB theory can have more appropriate
solutions related to programming languages.
http://www.princeton.edu/~jimpryor/courses/intro/notes/timetravel.html
http://www.princeton.edu/~jimpryor/courses/intro/notes/personal-id.html
)
Vladimir Odrljin Received on Sun Feb 19 2006 - 19:48:00 CET