Hi,
Good database design suggests that data should NOT be repeated across a DB system, so, the name of a product should only appear in ONE table only and a join has to link the tables in a query.
Assuming the following
Quote:
CREATE TABLE tblTags
(
ID INT( 5 ) NOT NULL ,
tagtext VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `ID` )
)
CREATE TABLE tblTagPos
(
ID INT( 5 ) NOT NULL ,
tagpos VARCHAR( 15 ) NOT NULL ,
PRIMARY KEY ( `ID` )
)
|
a select to find out where what tag is (a label on a warehousing shelve) one would use this:
Quote:
|
select tblTags.ID, tblTagPos.ID, tblTags.tagtext, tblTagPos.tagpos where tblTags.ID = tblTagPos.ID order by tblTag.ID
|
That works in a DB where the two sit in the (physically) same database, how would I do that in Java, knowing that a Connection A was set to a common database and Connection B "looks" to the actual "data" database, the tags never change, so I'd not really HAVE to copy this table to the data but leave it in the core...
Preceding the table names with the database name is a known trick to me, but that may fail, as tblTags would be in "core" and accessible via Connection A, and tblTagPos is in the "data" database and is subject to change (the positions on the shelves can change)...
It CAN be done, but (as far as I can see) it would involve two fetch runs, one to get the positions, and one to fetch the corresponding tag texts, about as efficient as a belgian politician
Hints? Tips anyone?
Tnx
Thor
DB question...
