Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Attribute name prefixes, domains, joins, ISO 11179

Re: Attribute name prefixes, domains, joins, ISO 11179

From: mAsterdam <>
Date: Wed, 30 Nov 2005 01:24:57 +0100
Message-ID: <438cf112$0$11068$> wrote:
> mAsterdam wrote:

>>Better, yes, but still bad. Which calendar do you use?
>>With dates the ownership is more problematic than with codes.
>>Who decided that you would be the authority?

> Can't I decide that I am the authority, and use whichever calender I
> feel like? After all, the attribute is specific to the system (an audit
> of when the row was added), and as the creator of the hypothetical
> system, I would be the only sensible authority. Taking my explanation
> of what I meant by "domain" originally, being the underlying machine
> type plus extra database constraints, the probability is that very few
> domains that I would use in my system would be wholly defined by
> external authorities: the constraints are, for the majority of
> attributes, going to be specific to my system, and so the domains are
> too.

Sure. For data you do not share, you are the sole authority.

> Or perhaps that's misguided, and the user defined type name should
> reflect only the mathematical/predefined domain constraints, and only
> the attribute name itself should reflect system specific constraints?

>>Let's assume that those issues are settled:
>>Which aspects of the domain do you/we want
>>to see reflected in the domain name?
>>First think about that and then see if you
>>can fit those into the scheme of things promoted
>>by the naming standard.

> If we are declaring new user defined types, I think I would want the
> domain name to include at least a representation term as per 11179, and
> probably a class term. Possibly not a qualification term, since
> qualification terms by definition do not change the fundamental
> meaning. But to be honest, I don't really care about domain names for
> the purpose of this discussion, I'm far more interested in column
> names.
>>>What about if they
>>>are the same domain and the attributes have the same name?
>>==table.column==    ==type==
>>Loan.Intrestrate    Percentage
>>Deposit.Intrestrate Percentage

> I assume that no meaningful join of Loan and Deposit can be made on
> InterestRate, but if that's the case, then are these attribute names
> misleading?

Misleading in another sense as well or just w.r. to the 'natural' join?

> I believe the "natural join" operation, if implemented,
> would attempt to join these two tables on the given attribute since
> they are of the same name and type, but there's certainly nothing
> natural about that join.

So don't do that, than. Not all things possible are meaningful. Or is that to simpleminded?

>> I'ld really like it more if you'ld
>>come up with your own example.

> Fair enough. How about a table of widgets which can have 2 different
> paints, one the top half and one on the bottom half:
> Paints(
> PaintCode PK,
> PaintIsWaterproof,
> PaintColourName,
> /* etc */)
> Widgets(
> WidgetNbr PK,
> TopHalfPaintCode FK references Paints
> BottomHalfPaintCode FK references Paints)
> "Code" is a representative term. In my data dictionary I would describe
> it as a short (less than, say, 8 characters) character string which
> does not have a meaningful order when sorted but which acts as a unique
> identifier.
> "PaintIsWaterproof" is an interesting one, it does not have an explicit
> representative term. I wonder if it is obvious that this is a boolean
> value? The "IsWaterproof" part as a whole is the 11179 property term.
> Harking back for a second, perhaps "Requesting" and "Approving" would
> also be better described as property terms, the wiki might have it
> wrong there. In any case, the same arguments about "dropping the term
> is allowed (but not always required!) in order to find relationshps"
> still applies.

Not sure I follow.

> "PaintColourName" is an easy one, its a simple
> Class/Property/Representation breakdown, where "name" is the sort of
> character data one would normally expect for a name.

Note that I'm definitely not familiar with this "[Object] [Qualifier] Property RepresentationTerm" column naming (e.g. I don't at all understand why the RepresentationTerm isn't optional for columns). Given the form that has to be filled in this all sounds reasonable to me.

> In widgets, WidgetNbr is Class + Representation. Nbr suggets
> alphanumeric data used for unique identification (explained more below)
> The interesting ones are tophalf... and bottomhalf... These are
> property components of the respective attribute names. We can drop them
> when looking for relationships. I have not included the word "widget"
> in the attribute names because that would suggest paint codes are part
> of the Widget class of things, where they are actually part of the
> Paint class of things. Hence the argument for FKs not getting the class
> term. And thinking about that a bit more, I guess it follows from that
> reasoning that all other attributes *should* get the class term as a
> prefix, regardless of how redundant it may seem (or how much extra
> typing it requires ;) But this gets back to my original question, and I
> can't say I'm convinced either way on the matter.
> To be explicit about this issue of redundancy: in, say, sql server, the
> fully qualified name of, say, the WidgetNbr attribute would be
> something along the lines of...
> <server name>.<database name>.Widgets.WidgetNbr

I never worked with sql server. Doesn't it have schemas ?

It depends on the scope of your conversation. When you are talking with your co-developer on changing Widgets.Nbr (s)he 'll understand what you are talking about.

> So it seems we could just as well call it
> <server name>.<database name>.Widgets.Nbr
> and not lose any information. But I'm not so sure about that.

Are you unsure about not losing any information?

> The fully
> qualified name tells us that Nbr is an attribute of the WIdgets table,
> yes. But that does not necessarily mean that it is "owned" by the
> Widgets class of thing!

What do you mean with "owned" in this context?

> We only know that it is because Widgets is also
> part of the attribute name. This tells us that you cannot have a
> WidgetNbr without a Widget. Contrast this with, say, TopHalfPaintCode,
> where the FQN would be
> <server name>.<database name>.Widgets.TopHalfPaintCode

Hm.... if you go form columname 'Nbr' to attributename by prefixing the singular objectname 'Widget' - why don't you do the same for TopHalfPaintCode to get WidgetTopHalfPaintCode?

There is another interpretation to this: The object you are painting is not the Widget, but another object (which for convenience does not have a seperate table in your database), namely the Widget_TopHalf. What is a part and what is a whole depends on point of view.

> Again, this tells us only that the column exists on the widgets table.
> To infer from this that you cannot have a PaintCode without a Widget
> would be incorrect.

Sounds correct to me.

> A PaintCode just happens to be an attribute that a
> widget can have, and it has two of them. You can certainly have
> PaintCodes even if you dont have Widgets, there they are in the Paints
> table. In fact, if we were to include "Widget" here, we would have
> "WidgetTopHalfPaintCode", which would contradictingly contain two
> different class terms ("Widget" and "Paint").

It may violate a rule (and then it helps to be explicit about which rule and why precisely it is violated) - but it is not in itself contradictory.

Or am I misunderstanding you?

>>Skipping '_Number' does not harm this interpretation:
>>Requesting_Employee would still be interpreted as 'the
>>Employee who requested the workorder', but - moving away
>>from semantics for now - the Employee table could have
>>multiple candidate keys so '_Number' might be necessary
>>to disambiguate.
>>This makes _Number a qualifier term in the sense that it is
>>"necessary to uniquely identify a data element"
>>(ISO/IEC 11179-5:1995 (E) p 8 under 'qualifier term').

> I'm sure number is a representation term in this example. It gives us
> some idea of what kind of values will be in the column. I would
> interpret a "number" attribute to be an identifying attribute, so
> either a PK, CK or FK column, with alphanumeric data on which math
> operations are invalid, but which can be sorted in a meaningful order.
> Invoice number, contract number, etc.

To me this looks like a kind of semi-soft typing (reminds me of "semi-structured data"). What good would that do?

> Now, this is only my gut feeling,
> to be certain of the intended meaning we could check the data
> dictionary. But even without a data dictionary, representative terms
> are enough to give a hint and most people will come up with reasonably
> similar notions given the same representative term. Or at least, not
> entirely dissimilar notions. Nobody is going to assume that a column
> with a representation term "number" is storing date values.

People - People ok
Machine - People ok
Machine - Machine not ok

>>>I have to say, ISO 11179 is not very clear about one point, which was a
>>>source of confusion for me. To quote the wiki entry on the WorkOrders
>>>"For Requirements_Text, the full name (i.e., the name that goes in the
>>>registry, or data dictionary) is WorkOrder_Requirements_Text; the Object
>>>part is omitted because it is declared in the WorkOrders table".
>>>Given this rule, why can't we call "WorkOrder_Number" simply "Number"?
>>Given this rule we can.

> But does that make sense? Given that number is usually indicative of a
> key attribute, and if we are using such keys in a system, you'd end up
> with a whole bunch of attributes in different relvars all called just
> "number". How would you know which joins were meaningful?

Humans by interpreting the external predicates. ISTM you want to automate that. Received on Tue Nov 29 2005 - 18:24:57 CST

Original text of this message