How to use a table more than once in relationship design?

[taylorkh]

Here is a question I posted to the unofficial forum - many views, no replies...

I have a database of my music CD collection which I am migrating from MS Access to Base. I have moved the tables into Base and am now building the relationships. The database has a table ARTISTS which which as the name implies contains the artist associated with the CD or Track (same as CD artist except on a various artist CD). Herein lies the rub. The same ARTISTS table is used to lookup the name of the artist associated with a CD or a TRACK.

I need to add the ARTIST table to the relationship design twice. Once as a One-to-many relation into CDS and separately as a One-to-many relation into TRACKS. If I try to add ARTISTS to the relationship design a second time nothing happens. Base simply shifts focus from the Add Tables dialog to the ARTISTS table which is already in the relationship design window.

I created a query (view) of the ARTISTS table but the relationship designer does not seem to allow views. It is not appropriate to physically duplicate the ARTISTS table under another name just to add it to the relationship. And no, it is not possible to relate TRACKS and CDS to the same ARTISTS table due to the "VARIOUS" artists situation.

Any ideas, suggestions???

TIA,

Ken

p.s. If anyone is interested I can make a copy of the database available on-line

p.p.s. I can add the table a second time when building a query and the results are correct. Here is some sample SQL:

SELECT "ARTISTS"."AR_NAME", "CDS"."CD_TITLE", "TITLES"."TI_TITLE", "ARTISTS_1"."AR_NAME" FROM "CDS" "CDS", "ARTISTS" "ARTISTS", "TRACKS" "TRACKS", "ARTISTS" "ARTISTS_1", "TITLES" "TITLES" WHERE ( "CDS"."CD_AR_KEY" = "ARTISTS"."AR_KEY" AND "TRACKS"."TR_CD_KEY" = "CDS"."CD_KEY" AND "ARTISTS_1"."AR_KEY" = "TRACKS"."TR_AR_KEY" AND "TRACKS"."TR_TI_KEY" = "TITLES"."TI_KEY" ) AND ( ( "ARTISTS"."AR_NAME" = 'VARIOUS' ) )

[RonIA]

I would say my suggestion right now would be use the query builder as your relationship...

You might try a different backend database engine. The built in HSQLDB engine in combination with OO.o Base can't handle it, but maybe if you got into the raw engine you could make the relationship. Not certain though.

[kabing]

There are probably a variety of different work arounds, depending on how you are needing to use the relationship.

In queries, you should be able to add a second instance of the "Artists"."AR_Name" field and give it an alias, although if I've understood you correctly, you've already found a work around by adding another instance of the table.

In data entry forms, if you are wanting to pull data from the AR_Name field in the Artists table to enter into one of the other tables, you should be able to use a Combo Box or List Box, without there being a formal relationship defined.

kabing

[TerryE]

This is a known problem. See

http://qa.openoffice.org/issues/show_bug.cgi?id=51787 and
http://qa.openoffice.org/issues/show_bug.cgi?id=56898

Note that the second of these is due to be fixed in OOo 2.4

[taylorkh]

Thanks for all the replies. I will remember to look again when 2.4 is released. My reason for wanting to establish the second relation into the ARTISTS table is to insure the referential integrity of the Artist key assigned to a TRACK. In reality I need to create a tie table between TRACKS and ARTISTS as a track may feature more than one Artist. I would still need to verify the entry in the tie table against ARTISTS. Someday I will make it that fancy and then go back through 4,000 + tracks and update.

Regards,

Ken

[TerryE]

This doesn't need a pig's ear type relationship. In fully normalised form yo would add an additional "join table" called, say, "ARTIST_ON_TRACK" which would associate the artist to the track.

[RonIA]

Agreed, but full normal form is usually not necessary for personal use databases.

[TerryE]

Ron, I fear that we might loose Ken in this debate, but I guess I am old fashioned -- Codd-wise. I think that you should start with Normal form for your LDM and and only denormalise when there are strong reasons. I started using proper RDBMS on VAX clusters in the late 80s. These "big clusters" had perhaps a 50th of the memory and processsing of a current PC and served 100s of online users. So runtime efficiency is rarely a valid reason. The only one that I could think applies is coding simplicity, and in general most tools work better with TNF.

Anyway, enough of the soapbox, before I die from being a boring old fart. Merry Christmas !!

[RonIA]

I'll let this discussion of normalization die, with one last sigh. SIGH!

Merry Christmas

[taylorkh]

The "pig's ear" relationship is a mystery to me, never heard that expression. As to normalization - been there done that. I started with dBase II on CP/M on an Osborne with 2 180 kB drives. I tracked worker radiation training records for 3,500 + employees. Took all day Saturday for my Epson FX80 to print 5 copies of a report needed first thing Monday morning.

When I took an early-early retirement a couple of years ago I was in a group which supported an enterprise work management and supply chain app using a DB2 database on MVS on a 10 processor IBM E-Server. And I have done a little on everything in between.

My little CDs database started out in FoxPro for DOS and in the early '90s I migrated it to Personal Oracle. However, my 486 based PC did not have the power to run Oracle and much of anything else so I moved the data to MS Access and built the front end in Visual Basic 4. The database design is rather thoroughly normalized except for the multiple artists on a track which has been discussed. I have found it to be a good example for explaining the concepts of relational databases. Most people can relate to an artist putting out several CDs and each CD having several tracks etc.

Some day I may move the thing to MySQL with a php front end. On the other hand - the artists who I listen too seem to have all retired, died or just release rehashed "best of" reissues. I doubt I have purchased 3 or 4 CDs this year so there is not too much motivation to rewrite.

Thanks again,

Ken

[TerryE]

The "pig's ear" relationship is a mystery to me, never heard that expression.

That's when you hav a table which has a relation back to itself. For example you might have an entity called employee who has a manager who will be an employee, giving a relationship from the table back onto itself. These are often should as a little loop back. I call them pig's ears, but no doubt other terms have been adopted in the past

You're Osborne II probably had 16Kb or 32Kb RAM as opposed to the current 2+GByte and an (8088?) instruction time of maybe 20 clocks @10MHz or 0.5MIP. Going flat out a Core2 can emit 2-3 instructions per clock cycle @ 3GHz and the instructions are faster. OK we get a lot of cache stalls which slows up things, but its still well over a 1,000x faster. Such is how thing have changed. Pity that the S/W hasn't got much better

[taylorkh]

My Osborne Executive had 128 KB of ram managed by the CP/M+ operating system to supply the 4 MHz Zilog Z80 processor with 16 KB pages swapped in and out to the maximum 64 KB which the processor could directly address. Quite superior to the 640 KB limit imposed by IBM/Microsloth in DOS. And it also had a full 80 column display!

Ken

[DrewJensen]

Well, I couldn't vary well stay away - what with Osbornes and pin impact printers running loose now could I.

I take it no one answered your question however.

The ability to add the second relation via the GUI designer is slated for the 2.4 release. You have always been able to do it by using standard DDL commands in the SQL window.

I'll assume that you have a table CD, ARTIST, each has a field ID and CD also has CD_ARTIST and TRACK_ARTIST so:

Tools > SQL ( open the sql window )

Code: Select all   Expand viewCollapse view


ALTER TABLE "CD" ADD CONSTRAINT "idx_track_artist" FOREIGN KEY ( "TRACK_ARTIST" ) REFERENCES "ARTIST" ( "ID" ) 

For full reference see the hslqdb docs.

Next time you open your relation designer you will see the second relation line.

Drew