Terminology question - set of related records in multiple tables

[Steve Jorgensen]

Terminology question:

Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.

I'm thinking the term might be graph, but I'm not at all certain of this.

Thanks,

Steve J

[Trevor Best]

Steve Jorgensen wrote:

Terminology question:

Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.

I'm thinking the term might be graph, but I'm not at all certain of this.

Thanks,

Steve J

Entity?

Invoice Header + Invoice Items = Invoice.

[Steve Jorgensen]

On Mon, 01 Nov 2004 08:36:03 +0000, Trevor Best <no****@besty.org.uk> wrote:

Steve Jorgensen wrote:

Terminology question:

Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.

I'm thinking the term might be graph, but I'm not at all certain of this.

Thanks,

Steve J

Entity?

Invoice Header + Invoice Items = Invoice.

I'm looking for a term that could apply to an arbitrarily complex set of
items, possibly with relationships among them, but all restricted by
relationship to a single key value somewhere. It could be as simple as an
invoice and its lines, or it could be as complex as a virtual schema for one
company in a database that supports multiple, independent companies
simultaneously, each with their own product catalog, customer, invoices, etc.,
and with no overlap allowed between records in different companies.

For example...

A collection of records comprising a single Company record and all other
records in all other tables that apply to that company and only that company.

Company
*CompanyId
CompanyName

Product
*ProductId
CompanyId
ProductName
(Unique CompanyId, ProductName)

Customer
*CustomerId
CompanyId
CustomerName
(Unique CompanyId, CustomerName)

Invoice
*InvoiceId
CompanyId
InvoiceNumber
(Unique CompanyId, InvoiceNumber)

InvoiceLine
*InvoiceLineId
CompanyId (part of both FKs to enforce same company)
InvoiceId (part FK to Invoice)
ProductID (part FK to Customer)
LineQty
UnitCost

[Konstantinos]

From the top of my head,
this is the universal relation restricted to tuples where CompanyID =
Company X

The universal relation (union of all relations) is how the database would
look in a completely unormalized state.

for instance Company A has Employees Nick and Tom
CompanyB has Employees Jerry and Scott
Each company also has an address.

In a normalized database you would have a table Company, for Employees and
for Addresses
But the universal relation would be something like this

Company Address
EmpName
A 27 Middle St, Boston, MA, USA Nick
A 27 Middle St, Boston, MA, USA Tom
B 40 Edge St, Boston, MA, USA Jerry
B 40 Edge St, Boston, MA, USA Scott

So what you are describing is the subset of the universal relation that
results from a selection on a particular value of a particular attribute.
In this case where Company = A, for instance.

Regards,
Konstantinos

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:s1********************************@4ax.com...

On Mon, 01 Nov 2004 08:36:03 +0000, Trevor Best <no****@besty.org.uk>
wrote:

Steve Jorgensen wrote:

Terminology question:

Is there a term for a set of records related directly or indirectly by
key
value in several tables? For example, a single invoice record and its
line
item records -or- a single customer, the customer's orders, the order
lines
for those orders, the customer's invoices, and the invoice lines for
those
invoices.

I'm thinking the term might be graph, but I'm not at all certain of
this.

Thanks,

Steve J

Entity?

Invoice Header + Invoice Items = Invoice.

I'm looking for a term that could apply to an arbitrarily complex set of
items, possibly with relationships among them, but all restricted by
relationship to a single key value somewhere. It could be as simple as an
invoice and its lines, or it could be as complex as a virtual schema for
one
company in a database that supports multiple, independent companies
simultaneously, each with their own product catalog, customer, invoices,
etc.,
and with no overlap allowed between records in different companies.

For example...

A collection of records comprising a single Company record and all other
records in all other tables that apply to that company and only that
company.

Company
*CompanyId
CompanyName

Product
*ProductId
CompanyId
ProductName
(Unique CompanyId, ProductName)

Customer
*CustomerId
CompanyId
CustomerName
(Unique CompanyId, CustomerName)

Invoice
*InvoiceId
CompanyId
InvoiceNumber
(Unique CompanyId, InvoiceNumber)

InvoiceLine
*InvoiceLineId
CompanyId (part of both FKs to enforce same company)
InvoiceId (part FK to Invoice)
ProductID (part FK to Customer)
LineQty
UnitCost

[Steve Jorgensen]

Thanks, but that's too long for a term. I was looking for a term I can use to
describe this concept within a larger term for a pattern related to such. I
guess I'll keep using "graph" (as in "directed graph") unless someone can tell
me a reason that's not an appropriate use of the term.

On Mon, 1 Nov 2004 04:28:45 -0500, "Konstantinos" <no*****@noemail.net> wrote:

From the top of my head,
this is the universal relation restricted to tuples where CompanyID =
Company X

The universal relation (union of all relations) is how the database would
look in a completely unormalized state.

for instance Company A has Employees Nick and Tom
CompanyB has Employees Jerry and Scott
Each company also has an address.

In a normalized database you would have a table Company, for Employees and
for Addresses
But the universal relation would be something like this

Company Address
EmpName
A 27 Middle St, Boston, MA, USA Nick
A 27 Middle St, Boston, MA, USA Tom
B 40 Edge St, Boston, MA, USA Jerry
B 40 Edge St, Boston, MA, USA Scott

So what you are describing is the subset of the universal relation that
results from a selection on a particular value of a particular attribute.
In this case where Company = A, for instance.

Regards,
Konstantinos

[Steve Jorgensen]

On Mon, 01 Nov 2004 11:43:38 GMT, Leythos <vo**@nowhere.org> wrote:

In article <s1********************************@4ax.com>,
no****@nospam.nospam says...

I'm looking for a term that could apply to an arbitrarily complex set of
items, possibly with relationships among them, but all restricted by
relationship to a single key value somewhere.

It's called a one to many relationship.

1 record relates to many sub-records in another table by key.

Not quite. 1-m relationship does not include the idea that we're only talking
about a group of records related to a single ancestor, nor the fact that if a
record is reachable by more than one 1-m path such as a junction between
descendents, that those must share the single starting ancestor record .

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:s1********************************@4ax.com:

I'm looking for a term that could apply to an arbitrarily complex
set of items, possibly with relationships among them, but all
restricted by relationship to a single key value somewhere.

A database?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[david epsom dot com dot au]

I think that what you are talking about is actually a kind
of set, rather than a kind of graph (I'll accept the overlap
of concepts), but unfortunately I can't think of a simple
term for the set of records that makes a complete record. :~(

In other contexts, I think you'd be talking about an
Object or an InfoSet. I'd think that there might also
be an XML term for what you've got in mind.

Graph, like Tree, can refer either to the structure or
to the data contained in that structure. (A tree a simple
kind of graph, a tree graph). Most database schemas are
trees, but relational data is not: the type tables are
branches on the schema graph, but loops on the data graph.

Neither Tree nor Graph specifically indicates that only
a single branch of the data is required, but to make that
point, sometimes the word Forest is introduced: A Tree
contains only related data, a Forest contains unrelated
data. (Because the unrelated date is excluded from the
graph, the graph will normally be a tree, even when taken
from a relational database).

Since I don't know a better word, I can't make any other
suggestions, but unless your graph includes loops, I'd
call it a tree.

If you're feeling brave, try the question again in comp.databases
(david)
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:an********************************@4ax.com...

Terminology question:

Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line item records -or- a single customer, the customer's orders, the order lines for those orders, the customer's invoices, and the invoice lines for those
invoices.

I'm thinking the term might be graph, but I'm not at all certain of this.

Thanks,

Steve J

[Steve Jorgensen]

On Tue, 2 Nov 2004 17:42:12 +1100, "david epsom dot com dot au"
<david@epsomdotcomdotau> wrote:

I think that what you are talking about is actually a kind
of set, rather than a kind of graph (I'll accept the overlap
of concepts), but unfortunately I can't think of a simple
term for the set of records that makes a complete record. :~(

In other contexts, I think you'd be talking about an
Object or an InfoSet. I'd think that there might also
be an XML term for what you've got in mind.

Graph, like Tree, can refer either to the structure or
to the data contained in that structure. (A tree a simple
kind of graph, a tree graph). Most database schemas are
trees, but relational data is not: the type tables are
branches on the schema graph, but loops on the data graph.

Neither Tree nor Graph specifically indicates that only
a single branch of the data is required, but to make that
point, sometimes the word Forest is introduced: A Tree
contains only related data, a Forest contains unrelated
data. (Because the unrelated date is excluded from the
graph, the graph will normally be a tree, even when taken
from a relational database).

Since I don't know a better word, I can't make any other
suggestions, but unless your graph includes loops, I'd
call it a tree.

If you're feeling brave, try the question again in comp.databases
(david)

First, I'm convinced you've understood my question. Thanks.

I know "graph" is not itself sufficient to describe the concept in full, but
it does imply a set of nodes reachable from a staring point, right? Would it
then be reasonably good terminology to say that a graph having no overlap with
other similar graphs is a "Partitioned Graph"?

The latest case where I actually came up with a schema like this that I wanted
a term for is a system for describing a data transformation process. I need
to represent enough of the schema of each source or destination system to
allow some additional tables to describe how data is transformed between them.
Essentially, the records that describe a single application's schema comprise
a "Partitioned Graph" if that's a workable term, and each app-to-app mapping
also describes a partitioned graph if the data in the metaschema tables is not
included.

The last prior case of this I ran into was a system that was supposed to
manage banks of test questions and exams given by several different companies,
but managed for them by a single company. Questions could be copied from
company to company, but only by exporting and re-importing, and they would be
actual content copies, not shared records.

[James Fortune]

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<v0********************************@4ax.com>. ..

I know "graph" is not itself sufficient to describe the concept in full, but
it does imply a set of nodes reachable from a staring point, right? Would it
then be reasonably good terminology to say that a graph having no overlap with
other similar graphs is a "Partitioned Graph"?

Keychain? :-).

James A. Fortune

[david epsom dot com dot au]

I never studied graph theory!!! But note that a partition
can include disconnected graphs (a Forest if the disconnected
graphs are acyclic). You get disconnected graphs in the
partition by not including the cut set.

(david)
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:v0********************************@4ax.com...

On Tue, 2 Nov 2004 17:42:12 +1100, "david epsom dot com dot au"
<david@epsomdotcomdotau> wrote:

If you're feeling brave, try the question again in comp.databases
(david)
First, I'm convinced you've understood my question. Thanks.

I know "graph" is not itself sufficient to describe the concept in full,

but it does imply a set of nodes reachable from a staring point, right? Would it then be reasonably good terminology to say that a graph having no overlap with other similar graphs is a "Partitioned Graph"?

The latest case where I actually came up with a schema like this that I wanted a term for is a system for describing a data transformation process. I need to represent enough of the schema of each source or destination system to
allow some additional tables to describe how data is transformed between them. Essentially, the records that describe a single application's schema comprise a "Partitioned Graph" if that's a workable term, and each app-to-app mapping also describes a partitioned graph if the data in the metaschema tables is not included.

The last prior case of this I ran into was a system that was supposed to
manage banks of test questions and exams given by several different companies, but managed for them by a single company. Questions could be copied from
company to company, but only by exporting and re-importing, and they would be actual content copies, not shared records.

[Clifford Heath]

We've been using the ad-hoc term "clump" to describe exactly
what you're talking about. A clump is a head record, and one
or more sets of related records found by traversing a
relationship (either to or from the head record), perhaps
applying a filter to the related records in each set.
Each related record may specify further related records in
the same way, including recursive queries against the same
table.

In this way, a clump description is a tree of relationship-
traversal instructions. We have an executable clump description
language and clump-query code as part of our in-house code
generator. The clump query code identifies "repeated" records
because it knows the unique identifiers on the tables, so a
clump may contain multiple references to a single record. The
generated C# code maps the clump to a DataSet, which allows the
whole clump to be serialised across web services to the UI
presentation layer. It's a very productive development
environment.

Clifford Heath, ManageSoft.

[Steve Jorgensen]

On Thu, 04 Nov 2004 12:28:18 +1100, Clifford Heath <[e-mail]> wrote:

We've been using the ad-hoc term "clump" to describe exactly
what you're talking about. A clump is a head record, and one
or more sets of related records found by traversing a
relationship (either to or from the head record), perhaps
applying a filter to the related records in each set.
Each related record may specify further related records in
the same way, including recursive queries against the same
table.

In this way, a clump description is a tree of relationship-
traversal instructions. We have an executable clump description
language and clump-query code as part of our in-house code
generator. The clump query code identifies "repeated" records
because it knows the unique identifiers on the tables, so a
clump may contain multiple references to a single record. The
generated C# code maps the clump to a DataSet, which allows the
whole clump to be serialised across web services to the UI
presentation layer. It's a very productive development
environment.

Clifford Heath, ManageSoft.

Does your definition of Clump reflect or not reflect the distinction that no 2
Clumps defined by the same head record have any common records? If not, would
it be fair to say that something like a Partitioned Clump would be a plausible
term for that in your lexicon?

[Clifford Heath]

Steve Jorgensen wrote:

Does your definition of Clump reflect or not reflect the distinction that no 2
Clumps defined by the same head record have any common records?
It explicitly allows the clump to contain the same record twice.
The clump fetcher identifies repeated records (which it can do
because it knows the unique keys), and instead of making a new
record instance, adds a reference to the previous record instance.
So although the same record may be retrieved twice from the DBMS,
it isn't stored twice.
If not, would
it be fair to say that something like a Partitioned Clump would be a plausible
term for that in your lexicon?

Hmm. We use "partition" for a different purpose (relevant to access
control), so no.

I think you're referring to a clump with a topology (record relationship
graph) which is a tree (rather than a directed graph, as ours supports).
In that case I suppose you could just call it a tree. Except that
doesn't catch the key concept, that each clump description identifies a
class of record groups having a specific topology.

I guess I'd just identify such clumps as having a tree topology.

BTW, the word "clump" came from the idea of uprooting weeds - you grab a
handful of stems, and pull up the clump of roots - whatever is attached,
comes up :-). Very informal naming, but I've never been given to
formality :-).

Clifford Heath.

[B. Hawes]

Steve Jorgensen <no****@nospam.nospam> wrote in message news:<an********************************@4ax.com>. ..

Terminology question:

Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.

I'm thinking the term might be graph, but I'm not at all certain of this.

Thanks,

Steve J

logical record

[david epsom dot com dot au]

> logical record

-------
http://www.archivists.org/glossary/t...initionKey=392

Definition
n. ~ A collection of related data elements treated as a conceptual unit,
independent of how or where the information is stored.
Notes
A logical record is defined by a particular data structure in an
application, independent of the physical characteristics and constraints of
the storage medium.
-------

Normally, the important characteristic of a Logical Record is it's
independance from physical form: the opposite of a Logical Record
is a Physical Record. A Physical Record normally implies a sequential
file, with fixed length or delimited records, but may also indicate
any other non-logical, non-relational, non-dictionary record format,
where logical records are constructed from physical records.
"B. Hawes" <bh****@satx.rr.com> wrote in message
news:d4*************************@posting.google.co m...

Steve Jorgensen <no****@nospam.nospam> wrote in message

news:<an********************************@4ax.com>. ..

Terminology question:

Is there a term for a set of records related directly or indirectly by key value in several tables? For example, a single invoice record and its line item records -or- a single customer, the customer's orders, the order lines for those orders, the customer's invoices, and the invoice lines for those invoices.

I'm thinking the term might be graph, but I'm not at all certain of this.
Thanks,

Steve J