Relationship Problem

[suchiate]

Hi Everyone,

I am currently working on two tables:

tbl1 ID (Replication ID), Field1, Field2, Field3

tbl2 ID (Replication ID), Field1

I have a one-to-one relationship for these two tables where tbl1 is the Primary key table. But, I cant insert any records into tbl2 as in Access it is AutoNumbered. Can someone teach me solve this kind of problem? Thanks.

 

[boblarson]

You add another field to Table 2 - tbl1ID and set it to Long Integer and that is where you insert the table 1 ID to tie the two tables together.

 

[suchiate]

Thanks for your reply Bob,

but what I want to implement is an inheritance concept, therefore, I want the primary key of tbl 2 be the foreign key itself from tbl 1. Can that be done in Microsoft Access?

 

[Rabbie]

but what I want to implement is an inheritance concept, therefore, I want the primary key of tbl 2 be the foreign key itself from tbl 1. Can that be done in Microsoft Access?

Then you need to ad a field to tbl 1 to hold the primary Key from tbl 2

As they are 1 to 1 it doesn't matter which way you link them

 

[suchiate]

So it will never work this way as illustrated?

TBL1 TBL2
FLD1 (PK) ----- FLD1 (FK, PK)
FLD2 FLD2A
FLD3

 

[boblarson]

So it will never work this way as illustrated?

TBL1 TBL2
FLD1 (PK) ----- FLD1 (FK, PK)
FLD2 FLD2A
FLD3

You could, I guess, make it work by creating a field in Table 2 that is Long Integer and setting it as PK. I can't see any benefit in doing so, but it's your call.

 

[suchiate]

You could, I guess, make it work by creating a field in Table 2 that is Long Integer and setting it as PK. I can't see any benefit in doing so, but it's your call.

Nah, Access wont allow relationships with different data types, AutoNumber must be related to AutoNumber.

Well the reason I am doing this is because, I dont need a separate PK for a child table since it should depend on the PK of the parent table. And the reason i am doing this is because some parents can have the child and some may not.

 

[Banana]

Erm, if you try to create a relationship with fields which both are autonumber, you will have problems. Big one. Access will try to create an autonumber for one, then do the same for another, and then complain that you would be creating a duplicate key, or creating a key that does not exist in another table.

You can in fact, relate an Autonumber to a Long field. If Access isn't letting you, then there's probably something else wrong.

As for "not all parents may have children"; how many data are we talking about? If you're splitting out a couple of fields, then I'd say just to leave it in one big table. It's much easier to maintain a single big table that's sparsely populated.

 

[boblarson]

An autonumber is a Long Integer - that is how you do use a PK from one table as FK in the other. You can't join Autonumbers as they would not stay synched anyway due to records being started, not finished and deleted, etc.

So what I said, is exactly how you normally do it, except that normally in table two you would have an autonumber as PK and then the ID from table one in there as Long Integer as FK instead of just marking the Long Integer FK as PK in table two.

 

[suchiate]

Ok I have my solution right here,

I set

Table1's PK as AutoNumber ReplicationID (GUID)
as the FK and PK for
Table2 which is Number of FieldSize ReplicationID

it's a simple thing which I dont know why i never thought of.. guess i need to brush up on Access, been using too much of MS SQL..


Can I continue asking some questions regarding Access Stored Queries VS MS SQL Stored Procedures here?

 

[dfenton]

Ok I have my solution right here,

I set

Table1's PK as AutoNumber ReplicationID (GUID)
as the FK and PK for
Table2 which is Number of FieldSize ReplicationID

it's a simple thing which I dont know why i never thought of.. guess i need to brush up on Access, been using too much of MS SQL.

Doesn't look like it's an issue with Access so much as a lack of familiarity with the SQL theory behind 1:1 relationships.

In any event, I'd warn you away from using ReplicationIDs in Access. Here's an explanation of why:

http://trigeminal.com/usenet/usenet011.asp?1033

Random Autonumbers are going to be practical for nearly any situation and not cause any of the problems that GUIDs do.

 

[The_Doc_Man]

During my discussion with Banana in the recent thread on Nulls, I had a long explanation of why one/one relationships should be rare. In particular, one thing you said really bothers me.

some parents can have the child and some may not.

If this is true then you do NOT have a one/one relationship. You have a one/many relationship. One/one has a highly specific usage. Wade through the discussion started by Banana on Nulls. You'll find the post in question.

 

[suchiate]

During my discussion with Banana in the recent thread on Nulls, I had a long explanation of why one/one relationships should be rare. In particular, one thing you said really bothers me.



If this is true then you do NOT have a one/one relationship. You have a one/many relationship. One/one has a highly specific usage. Wade through the discussion started by Banana on Nulls. You'll find the post in question.

What I am raising about is not on the Physical Point of View.. but from the conceptual point of view.

It is not a One-to-One relationship as the Child table, would only inherit the PK attribute from the Parent table whereby the parent table has nothing from the child table, no key or anything.

See the scenarios below:

Table1
filed1(PK)
Car
Engine

Table2
field1(FK,PK)
Turbo

I dont see this as a One-to-One relationship but an Inheritance relationship, since the Table 2 (Child) has no related field in Table1 itself and some cars may have turbo or may not.

If u realized, from the first post till this post, i never said i was working on a One-to-One relationship and I did mention and illustrated on my inheritance example. Anyhow, its all good because I have my problem solved, and the example i gave above with only one child field in Table 2 is jus an example. I have a bigger child table i am working with, therefore, if combining everything in table 1, would destroy the performance definately.

To add on, this is not a One to Many relationship as well, since one parent can only have one child, so I call this mutual inheritance relationship, Note that the FK is also the PK on my child table, therefore, one child will only have one parent and one parent will only have one child. Again, this is not one-to-one but Inheritance.

 

[The_Doc_Man]

I have a one-to-one relationship for these two tables where tbl1 is the Primary key table.

from 07-09-2007 06:52 PM

It is not a One-to-One relationship

from 07-14-2007 11:44 PM

It would help if you made up your mind...

AutoNumber must be related to AutoNumber.

Pardon me, but bullsh|t. If you are going to ask the people who use Access on a regular basis to help you and then are arrogant enough to try and correct us on something, at least you should be smart enough to correct us correctly.

I dont see this as a One-to-One relationship but an Inheritance relationship, since the Table 2 (Child) has no related field in Table1 itself and some cars may have turbo or may not.

I see someone who does not understand that relationships are bidirectional.

There is NO REASON to have the case you described in the 07-14-2007 11:44 PM post. Turbo could be so simple as a yes/no field on the first table. It is TOTALLY unnecessary to have two tables for this data set. There is EVERY REASON to collapse the tables into a single table with a couple of yes/no attribute fields. YES, it has a turbo. YES, it has chrome tailpipes. YES, it has more than three outstanding traffic tickets for excessive noise pollution. NO, it is not fully paid for yet. Things like that.

What I am raising about is not on the Physical Point of View.. but from the conceptual point of view.

If your "model" of the real world doesn't correspond to the real world, what good is it? You are either being very dense with all the advice everyone is trying to give you or you are very poor in expressing yourself. I don't know which but I've got to say that you are being recalcitrant. Which sometimes has the potential to turn us off.

If you really want help, fine. Ask your questions. If you are looking for an argument, go find an old episode of "Monty Python's Flying Circus." You'll find argument is in room 114 down the hall.

 

[suchiate]

from 07-09-2007 06:52 PM

from 07-14-2007 11:44 PM

It would help if you made up your mind...



Pardon me, but bullsh|t. If you are going to ask the people who use Access on a regular basis to help you and then are arrogant enough to try and correct us on something, at least you should be smart enough to correct us correctly.



I see someone who does not understand that relationships are bidirectional.

There is NO REASON to have the case you described in the 07-14-2007 11:44 PM post. Turbo could be so simple as a yes/no field on the first table. It is TOTALLY unnecessary to have two tables for this data set. There is EVERY REASON to collapse the tables into a single table with a couple of yes/no attribute fields. YES, it has a turbo. YES, it has chrome tailpipes. YES, it has more than three outstanding traffic tickets for excessive noise pollution. NO, it is not fully paid for yet. Things like that.



If your "model" of the real world doesn't correspond to the real world, what good is it? You are either being very dense with all the advice everyone is trying to give you or you are very poor in expressing yourself. I don't know which but I've got to say that you are being recalcitrant. Which sometimes has the potential to turn us off.

If you really want help, fine. Ask your questions. If you are looking for an argument, go find an old episode of "Monty Python's Flying Circus." You'll find argument is in room 114 down the hall.

First of all, The_Doc_Man,

I am not trying to challenge and I'm especially not trying to find an argument with anyone, maybe I AM bad in expressing myself or you read with anger or can't accept certain things, but whichever, I am sorry for the misunderstanding.

Regarding the AutoNumber to AutoNumber issue, boblarson suggested me to use Long Integer, that's why I replied - Nah it wont allow different data types, but afterwards, I tried again myself and I wrote the post as solving my problem that I place it as placing the Child table as Integer - Replication ID because I did memtion in the beginning I am working with Replication IDs.

Lastly, regarding the 'Turbo' issue, I also have mentioned that

"the example i gave above with only one child field in Table 2 is jus an example. I have a bigger child table i am working with, therefore, if combining everything in table 1, would destroy the performance definately"

Anyway, just to double clarify that, I have never wanted to challenge or argue with anyone, after all, i believe there is no Right or Wrong design but Good or Bad design, and i consider all these as Discussions rather than Arguments.

Hope you won't get me wrong and I do appreciate everyone who have replied.

 

[The_Doc_Man]

OK, I apologize. Sometimes I read something that wasn't intended. Forgive me if you can.

Back to the issue at hand. I will respectfully suggest that you carefully examine your real-world relationships to decide whether the tables are always one-one or whether sometimes they are one/none. In the latter case, you really should code the tables as one/many (knowing you will sometimes get back none and more often you will get back one.) The slightly relaxed rules of one/many relationships make Access work better in the "none" case.

If you decide that you really do have a one/one case, I repeat with all vigor that you should find that thread between me and Banana to find the discussion on why one/one relationships are - and should be - so rare in any really well-designed database.

 

[suchiate]

OK, I apologize. Sometimes I read something that wasn't intended. Forgive me if you can.

Back to the issue at hand. I will respectfully suggest that you carefully examine your real-world relationships to decide whether the tables are always one-one or whether sometimes they are one/none. In the latter case, you really should code the tables as one/many (knowing you will sometimes get back none and more often you will get back one.) The slightly relaxed rules of one/many relationships make Access work better in the "none" case.

If you decide that you really do have a one/one case, I repeat with all vigor that you should find that thread between me and Banana to find the discussion on why one/one relationships are - and should be - so rare in any really well-designed database.

Of course, no hard feelings between us

After all, you are just trying to give the best advice and suggestion you could to anyone =) and I appreciate that and I hope everyone does too.

Actually the actual case I am dealing with is a Multi Level Marketing System, whereby members can be in one to many "Bonus Plans". So, I plan to have a Universal Member Login, and the members will have many child tables which are Plan A, Plan B, and so on where each plan have many fields of their own required information.

So if the member is registered to a certain plan, they will have one and only one record in the specific plan table. I am doing this so that we can determine easily which members exist in which plans and so on by one Unique GUID from the Universal Member table.

Based on this real life scenario, could you comment and advice on my current proposed design?

Thank you very much.

 

[The_Doc_Man]

OK, you have a scheme with a single member table and a series of PLAN tables. The relationship is such that for all plan tables taken together, you would have one/one with a member, but for any single plan table, it might be very sparsely populated when compared to the member table.

The first question I have is this; Are the various plans so different that they cannot all be in the same Plan table with a code to distinguish between plan A, plan B, plan C, etc.? In other words, are the fields in the plan tables actually that different?

My suggestion is that if it is humanly possible, you should try to re-merge those tables. If in so doing, it happens that a couple of fields have to be empty for plan A and a couple of different fields would be empty for plan B, but they could all be in the same plan table, you would be in really good shape.

Also, Banana and I worked on the issues of normalization. Find the discussions regarding his "Three-legged table" for a similar problem to yours. The solution involves some serious data abstraction, but if you can get to a point where you are really normalized properly, it would make your life so much easier.

Some theorists might disagree with me (might? HAH!) but when you have that split with different plan tables, I believe that is one of the more subtle forms of denormalization. Possibly 4th-normal. You can read the Wikipedia article on database normalization or Google-search that topic if you aren't familiar with 4th or 5th normal forms off the top of your head. They are really quite abstract compared to 1NF, 2NF, 3NF tables.

 

[Banana]

And so you don't have to search for it... Here's the thread

 

[suchiate]

Alright, thanks doc and banana,

I will try to understand the entire scenario of the system first as I am taking over the previous database and programmer's tasks, 1 taking over 2 but their design really sucked.. so I have to redesign but the system scope is so big that I think I should study everything first before just starting off blindly like i am doing now with the Members and Login tables, but im rushing this because there's very little time given... Guess I better request for more time to study the system more to play safe in future.

Thank you guys for the suggestions and guidelines as well as the normalization tips.