[glue-wg] Modeling Relationships in GlueSQL

[Felix Nikolaus Ehm]

Hi Timo,


On Fri, 28 Mar 2008, Timo Baur wrote:

> Hello Felix,
> 
> we here at DMON now try to feed first data into our Glue2.0 based DB.
> We have some issues regarding the unique IDs, while trying to model 
> relationships between ComputingResources,
> Locations and AdminDomains.
> 
> What we - still unsucessfully - tried to do is:
> 
> REPLACE INTO Location (...) VALUES (...);
> SELECT @locationID:=id FROM Location WHERE localID="...";
> REPLACE INTO AdminDomain (...,locationID) VALUES (..., at locationID);
> 
> 
> mysql> SELECT uniqueID,locationID FROM AdminDomain;
> +--------------------------------------+------------+
> | uniqueID                             | locationID |
> +--------------------------------------+------------+
> | https://dmon-unic.fz-juelich.de:9115 |        197 |
> +--------------------------------------+------------+
> 
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
> constraint fails (`GLUE20/AdminDomain`, CONSTRAINT
> `fk_AdminDomainlocationID` FOREIGN KEY (`locationID`) REFERENCES
> `Location` (`id`))
> 
It seems to me that you didn't specify a ParentTypeID which must exist in 
the EntryTable when you do the REPLACE INSERT.
This is mandatory to identify of which type the parentID is. That could 
cause the constraint error.I'll recheck the cases with the schema.



> We are wondering if we really should ask the DB for the id in the RS or 
> if there is another
> way to relate the tables by only using the unique id of the DS.
> 
I also thought of dropping the numerical presentation of the id and 
instead using the document schema ID. But then table joins with several 
key elements would get quite complicated.

Example :
If you want to find the Endpoint which serves a certain Share then you 
would need the localId and parentID from the Share in the EndpointShareLNK 
table. With the current solution you only have one id you need to join.

Another reason I have in mind is the fact when doing joins string 
comparison operations are more heavy-weight than number comparisons. 
Furthermore, modern database systems also support partitioning which is an 
effective way of speeding up search access in large datasets. However, 
MySQL for example supports currently (5.1) only the datatype integer. 

Generally, I do agree with you that this argumentation looses it 
importance if you have exactly one uniqueID for a table (Service, 
Endpoint, etc.). 

I am currently updating the schema to the latest (draft 33) of GLUE 2.0. 
There isn't much change for the main enties and Computing but for the 
storage part. I'll check again the 'numerical id issue' and sent you an 
update as soon as I have finished it.


Cheerio,
	Felix