|
|
You're in the
sql server (alternate)
group:Terminology question - set of related records in multiple tables
sql server (alternate):
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,
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 [quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:s1ubo0598nmheb2k8udg65djjj5032ipfv@4ax.com... > On Mon, 01 Nov 2004 08:36:03 +0000, Trevor Best <nospam@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
[quoted text, click to view]
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?
[quoted text, click to view]
On Mon, 01 Nov 2004 08:36:03 +0000, Trevor Best <nospam@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
In article <s1ubo0598nmheb2k8udg65djjj5032ipfv@4ax.com>,
nospam@nospam.nospam says... [quoted text, click to view] > 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. -- -- spamfree999@rrohio.com
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. [quoted text, click to view] On Mon, 1 Nov 2004 04:28:45 -0500, "Konstantinos" <noemail@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
[quoted text, click to view]
On Mon, 01 Nov 2004 11:43:38 GMT, Leythos <void@nowhere.org> wrote: >In article <s1ubo0598nmheb2k8udg65djjj5032ipfv@4ax.com>, >nospam@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
In article <gcico01uvomgr6su4583o7u4v795tjdac4@4ax.com>,
nospam@nospam.nospam says... [quoted text, click to view] > On Mon, 01 Nov 2004 11:43:38 GMT, Leythos <void@nowhere.org> wrote: > > >In article <s1ubo0598nmheb2k8udg65djjj5032ipfv@4ax.com>, > >nospam@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 . 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@rrohio.com
Steve Jorgensen <nospam@nospam.nospam> wrote in
news:s1ubo0598nmheb2k8udg65djjj5032ipfv@4ax.com: [quoted text, click to view] > 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
On Tue, 2 Nov 2004 17:42:12 +1100, "david epsom dot com dot au"
[quoted text, click to view] <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
[quoted text, click to view]
Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<v0ceo0545vh8gubt28aifl95frk9es4bg0@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? :-).
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) [quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:anpao0t6qpum45rqj873q1fp5sc6h0aqqi@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
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) [quoted text, click to view] "Steve Jorgensen" <nospam@nospam.nospam> wrote in message news:v0ceo0545vh8gubt28aifl95frk9es4bg0@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.
[quoted text, click to view]
On Thu, 04 Nov 2004 12:28:18 +1100, Clifford Heath <no@spam.please> 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
[quoted text, click to view]
Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<anpao0t6qpum45rqj873q1fp5sc6h0aqqi@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
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.
[quoted text, click to view]
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. [quoted text, click to view] > 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 :-).
[quoted text, click to view]
> logical record ------- http://www.archivists.org/glossary/term_details.asp?DefinitionKey=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. [quoted text, click to view] "B. Hawes" <bhawes@satx.rr.com> wrote in message news:d478259.0411040912.6565b29d@posting.google.com... > Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<anpao0t6qpum45rqj873q1fp5sc6h0aqqi@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 >
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |