Combine or add two unrelated tables into one

[stevo99]

I need guidance to combine 2 nearly unrelated tables into one new "Resultant" table. The PK of the Resultant must point to a record in one or the other of the two contributing tables. If my Resultant table includes as FKs, PKs from the contributing tables, then won't each record in the Resultant be null or 0(?) for the other table's PK? Would that work?
(new)PK-FK1-FK2
1-0-1,
2-1-0,
3-0-2,
4-2-0
5-3-0

Thanks for any help with this.

 

[Remou]

It very much depends on what you want to do with the table after you have created it. You may not even need to create a new table, a Union query may suit.

http://lessthandot.com

 

[stevo99]

1st, thanks for responding. What if the PK of Resultant table is particularly central and used in many relationships? I just read briefly about union queries and it seems that might work. Are there clear-cut limitations or restrictions on use of the new PK? or probably on use of the two FKs? Thanks

 

[Remou]

You can have as many FKs as you want, but only one PK. Are you thinking of a junction table (

http://en.wikipedia.org/wiki/Junction_table)?

http://lessthandot.com

 

[stevo99]

I don't think so. The two tables with 5-fields each & (data types). They have the field FunctionID in common.

TABLE1: tblMYHORSES
*MyHorsesID (AN)
TypeID (T)
FunctionID (N)
CRSNo (T)
WeDontUseNames (T)

Table2: tblOTHERSTABLESHORSES
OtherHorsesID (AN)
TyCode (T)
FunctionID (N)
CRPNo (T)
Name (T)

tblHORSES (=Resultant)
*HorsesID (AN)
FK-tbl1 [MYHORSESID] (N)
FK-tbl2 [OTHERHORSESID] (N)

The race horse comes from one of the two tables:


tblRaceSetup
RaceSetupID
Horse (FK from Resultant)
Jockey (FK from Jockeys)

Below is what I'm trying to accomplish:

tblRACES
*RaceID (AN)
RaceSetupID (FK)
OtherStuff

Not sure if this helps or confuses. Thanks Remou!

 

[Remou]

The problem is your table set up. You need:

A table Horses
HorseID
HorseDetails

A table Stables
StableID
StableDetails

A table Horse Stable
HorseID
StableID
JoinDate
EndDate

A table Races
RaceID
RaceDateTime
RaceDetails

And finally a table Race Runners
RaceID
HorseID

http://lessthandot.com

 

[stevo99]

Okay. (I do have the table Races ~as shown.) I don't get this part:

If I understand correctly, it looks like you have combined MyHorses & OtherStableHorses and created the table Stables to keep them separate. Sok but the fields of the two tables are completely different, like metric vs imperial but not inter-convertible. That's the part that won't work. Basically, I want to keep the table HorseStable and use it's PK(AN) in table Race Runners? But again, apparently, that's what I can' do? Maybe I misunderstood.

table Race Runners
*RRID
RaceID
HorseStableID
RRDetails

Much thanks for your responses

 

[stevo99]

Remou; Alternatively, the union query looks ok. How would I go about creating a PK to use as the 1-side in relationships? After the initial creation of a table HorsesStable, could I turn it into an append query that runs once there any new entries in either table? Thanks!

 

[Remou]

I do not see how the fields can be completely different. Both your horses and other stable horses must have a great deal in common, if there are additional fields, they can go in one or two subclass tables (

http://en.wikipedia.org/wiki/Subclass_(computer_science)).

You could also consider a horse properties table that list the various properties that could be associated with a horse.

The main point is that horses should go in a super class table, as should stables, these are fixed. The stable a horse belongs to can change, so you need a stable/horse table to show this data.

The stable/horse table is not used to set up the runners table, the horse table gives you the runners. The runners table can include additional fields such as where the horse placed and the jockey id.

If you use a set-up similar to my suggestion, queries will allow you to get any data relevant to a runner, such as which stable the horse belongs to.

You may wish to read

http://www.r937.com/relational.html

http://lessthandot.com

 

[Remou]

I had not seen you second post when I posted. Please consider how relational databases work, and work within that frame. It will be mush easier in the end.

http://lessthandot.com

 

[stevo99]

Thanks Remou. I will go back and review my tables