472,809 Members | 3,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,809 software developers and data experts.

SQL relationships

This is more of a general SQL relationship question than a
PostgreSQL-specific question, although I am using PostgreSQL to
implement this.

I have a number of data structures which I am calling "units". A unit
has a name, number and a data structure. I store a unit as a single
record in a table with fields for the name and number and a field for an
XML string which represents the data on the unit.

- table "units":

numb name XML
1 unit1 <data ... />
2 unit2 <data refers_to="unit1" ... />
3 unit3 <data refers_to="unit2" ... />

There are relationships between these units which I would like the
database to understand. If one unit refers to other units, I would like
the database to return all referenced units in a single SELECT call.

- loading 1 would return unit1
- loading 2 would return unit2 and unit1
- loading 3 would return unit3, unit2 and unit1

I currently handle this outside of the database with multiple SELECT
calls - I read a single unit from the database, examine the XML data for
references to other units, load those units, rinse and repeat until each
referenced unit is loaded. I achieve the objective of loading all units
that are referenced by originating unit, but I have to make multiple
SELECT calls to get there.

I don't expect the database to understand the XML data structure that
describes the unit relationships, of course. Instead, I would like to
put the right table structure in place to begin with and use SQL syntax
to make a single SELECT call from one table that returns one or more
units from another table.

My best guess right now is a "relationships" table:

rel_id unit_numb rel_numb
1 1 1
2 2 2
3 2 1
4 3 3
5 3 2
6 3 1

to load unit3, I would "SELECT rel_numb FROM relationships WHERE
unit_numb = 3" and then load all records from the "units" table whose
number matches the returned rel_numb values.

This seems cacky and smells of a non-SQL approach. I don't know whether
I would be able to SELECT it all with a single call or whether I would
have to separate it into 2 calls. There is also a certain amount of
duplication - rel_id 6 should be unnecessary because rel_id 5 should
imply rel_id 3 (in other words, unit3's relationship to unit1 exists
because of the unit3->unit2 and unit2->unit1 relationship).

Am I heading in the right direction? I am an experienced Perl programmer
but my experience with SQL is limited. Can someone tell me how much of
this SQL can do for me, given the right table setup?


Jul 19 '05 #1
0 1701

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Ed | last post by:
I am running Access 2002 and just ran the built in Access wizard for splitting a database into a back end (with tables) and front end (with queries, forms, modules, etc.). After running the...
by: Megan | last post by:
Hi Everybody- I know that this is a really, really long post, but I wanted to try to give you as much background as possible. So here's a quick overview of the issues I'm asking for help with:...
by: Max | last post by:
Hi. I really hope someone can help me. Going slowly insane with this problem. I have a two Access 2000 databases. One is the backend containing tables and some admin queries. The other is the...
by: davegb | last post by:
I'm totally new to relational database design. My boss has asked me to create a database of information on the employees in our group. Seemed to me like a simple application to learn the ropes. A...
by: Dixie | last post by:
I need to delete some relationships in code. How do I know what the names of those relationships are?
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
by: celinesuzzarini | last post by:
Hi all, I have split my database a while ago, and now, I want to add a table with relationships to other existing tables. I open the BE, create my table, and then go to the relationships...
by: ARC | last post by:
Hello all, Prior to going live with my app, I have questions on relationships theory. My prior app was done in Access 97, and I did NOT use relationships at all. I have 65 tables in my...
by: John Google | last post by:
Hi, Access 2002. I import tables from another database where I only copy the definition and not the data. I select the Import Relationships option on the import dialog. After I do the...
by: Phil Stanton | last post by:
I have a FE, BE database on my home computer and a duplicate on the office computer. Both use Ak2 on Windows XP. The one at home appears normal. The BE consists of about 50 tables with numerous...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.