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]

Re: Terminology question - set of related records in multiple tables

Steve Jorgensen wrote:[color=blue]
> 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[/color]

Entity?

Invoice Header + Invoice Items = Invoice.

[Steve Jorgensen]

Re: Terminology question - set of related records in multiple tables

On Mon, 01 Nov 2004 08:36:03 +0000, Trevor Best <nospam@besty.o rg.uk> wrote:
[color=blue]
>Steve Jorgensen wrote:[color=green]
>> 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[/color]
>
>Entity?
>
>Invoice Header + Invoice Items = Invoice.[/color]

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]

Re: Terminology question - set of related records in multiple tables

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" <nospam@nospam. nospam> wrote in message
news:s1ubo0598n mheb2k8udg65djj j5032ipfv@4ax.c om...[color=blue]
> On Mon, 01 Nov 2004 08:36:03 +0000, Trevor Best <nospam@besty.o rg.uk>
> wrote:
>[color=green]
>>Steve Jorgensen wrote:[color=darkred]
>>> 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[/color]
>>
>>Entity?
>>
>>Invoice Header + Invoice Items = Invoice.[/color]
>
> 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[/color]

[Leythos]

Re: Terminology question - set of related records in multiple tables

In article <s1ubo0598nmheb 2k8udg65djjj503 2ipfv@4ax.com>,
nospam@nospam.n ospam says...[color=blue]
> 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.[/color]

It's called a one to many relationship.

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

--
--
spamfree999@rro hio.com
(Remove 999 to reply to me)

[Steve Jorgensen]

Re: Terminology question - set of related records in multiple tables

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, "Konstantin os" <noemail@noemai l.net> wrote:
[color=blue]
>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[/color]

[Steve Jorgensen]

Re: Terminology question - set of related records in multiple tables

On Mon, 01 Nov 2004 11:43:38 GMT, Leythos <void@nowhere.o rg> wrote:
[color=blue]
>In article <s1ubo0598nmheb 2k8udg65djjj503 2ipfv@4ax.com>,
>nospam@nospam. nospam says...[color=green]
>> 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.[/color]
>
>It's called a one to many relationship.
>
>1 record relates to many sub-records in another table by key.[/color]

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 .

[Leythos]

Re: Terminology question - set of related records in multiple tables

In article <gcico01uvomgr6 su4583o7u4v795t jdac4@4ax.com>,
nospam@nospam.n ospam says...[color=blue]
> On Mon, 01 Nov 2004 11:43:38 GMT, Leythos <void@nowhere.o rg> wrote:
>[color=green]
> >In article <s1ubo0598nmheb 2k8udg65djjj503 2ipfv@4ax.com>,
> >nospam@nospam. nospam says...[color=darkred]
> >> 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.[/color]
> >
> >It's called a one to many relationship.
> >
> >1 record relates to many sub-records in another table by key.[/color]
>
> 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 .[/color]

you're thinking at the wrong level - you just described a 1-M
relationship. Your fault is in thinking too low in the tree. you can
have any number of 1-m relationships with each other.

--
--
spamfree999@rro hio.com
(Remove 999 to reply to me)

[David W. Fenton]

Re: Terminology question - set of related records in multiple tables

Steve Jorgensen <nospam@nospam. nospam> wrote in
news:s1ubo0598n mheb2k8udg65djj j5032ipfv@4ax.c om:
[color=blue]
> 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.[/color]

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]

Re: Terminology question - set of related records in multiple tables

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" <nospam@nospam. nospam> wrote in message
news:anpao0t6qp um45rqj873q1fp5 sc6h0aqqi@4ax.c om...[color=blue]
> 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[/color]
line[color=blue]
> item records -or- a single customer, the customer's orders, the order[/color]
lines[color=blue]
> 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[/color]

[Steve Jorgensen]

Re: Terminology question - set of related records in multiple tables

On Tue, 2 Nov 2004 17:42:12 +1100, "david epsom dot com dot au"
<david@epsomdot comdotau> wrote:
[color=blue]
>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)[/color]

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 "Partitione d 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 "Partitione d 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]

Re: Terminology question - set of related records in multiple tables

Steve Jorgensen <nospam@nospam. nospam> wrote in message news:<v0ceo0545 vh8gubt28aifl95 frk9es4bg0@4ax. com>...[color=blue]
> 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 "Partitione d Graph"?[/color]

Keychain? :-).

James A. Fortune

[david epsom dot com dot au]

Re: Terminology question - set of related records in multiple tables

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" <nospam@nospam. nospam> wrote in message
news:v0ceo0545v h8gubt28aifl95f rk9es4bg0@4ax.c om...[color=blue]
> On Tue, 2 Nov 2004 17:42:12 +1100, "david epsom dot com dot au"
> <david@epsomdot comdotau> wrote:
>[color=green]
> >
> >If you're feeling brave, try the question again in comp.databases
> >
> >
> >(david)[/color]
>
> First, I'm convinced you've understood my question. Thanks.
>
> I know "graph" is not itself sufficient to describe the concept in full,[/color]
but[color=blue]
> it does imply a set of nodes reachable from a staring point, right? Would[/color]
it[color=blue]
> then be reasonably good terminology to say that a graph having no overlap[/color]
with[color=blue]
> other similar graphs is a "Partitione d Graph"?
>
> The latest case where I actually came up with a schema like this that I[/color]
wanted[color=blue]
> a term for is a system for describing a data transformation process. I[/color]
need[color=blue]
> to represent enough of the schema of each source or destination system to
> allow some additional tables to describe how data is transformed between[/color]
them.[color=blue]
> Essentially, the records that describe a single application's schema[/color]
comprise[color=blue]
> a "Partitione d Graph" if that's a workable term, and each app-to-app[/color]
mapping[color=blue]
> also describes a partitioned graph if the data in the metaschema tables is[/color]
not[color=blue]
> 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[/color]
companies,[color=blue]
> 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[/color]
be[color=blue]
> actual content copies, not shared records.[/color]

[Clifford Heath]

Re: Terminology question - set of related records in multiple tables

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]

Re: Terminology question - set of related records in multiple tables

On Thu, 04 Nov 2004 12:28:18 +1100, Clifford Heath <no@spam.please > wrote:
[color=blue]
>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.[/color]

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?