Re: How to model searchable properties of an entity

In message <yL-dnWnPUNPANbncRVn-hA_at_comcast.com>, Laconic2 <laconic2_at_comcast.net> writes

>The better way is to start from the requirements and derive a conceptual
>data model, and a companion conceptual process model.

Then add an event model and you have SSADM.

[...]

>Somewhere in this long discussion, someone asked "why have a load of tables
>for what is basically the same abstraction?"
>
>That question deserves a more thoughtful answer than I have been able to
>give. Before you can deal with that question, you have to be able to
>answer the question "when are two abstractions the same abstraction?" This
>sounds like an easy question, but it's not.

The glib answer to the question is "because there are many entities in the ERD."

My answer to people who want to move the physical design away from the logical data structure is to assert that there is always a price to pay for doing that.

>
>With regard to "parts" , there is a level of abstraction at which they are
>all the same. If I want to order a wheel and a tie rod,
>I probably have to know exactly the same data about each to fill out the
>order form. I think you've outlined that data below.

Some methodologies include the concept of sub-entities. That is entities which form a logical grouping. "Parts" is one of those. The bill of material problems is often used as an example of something where OO succeeds and relational databases fail. Personally I have no problem at all with recursive relationships but I've been working with hierarchical systems since my first contact with a computer at the age of about 7.

I have a sneaking suspicion that there is a grand unifying theory that covers both OO and relational theory. I see relational systems as accessing data using Cartesian co-ordinates and OO systems as accessing the same data using polar co-ordinates. I'm waiting for someone to invent relational geometry.

>
>But if I'm diagnosing to determine whether a new wheel or a new tie rod (or
>both) will fix the problem, I probably need to treat wheels and tie rods as
>different types of entities, because they play different roles in the
>diagnosis model.
>
>Diagnosis and order taking are both "valid" ways of looking at the data.
>Depending on the requirements, the system may have to support both.
>
>>Actually, I think my inclination now would probably
>> still be more pragmatic: for the most part, all those inventory items
>> can be considered similar: their most important attributes would be
>> stuff like location (grid ref), cost, date installed, date last
>> inspected, make, model, etc. common to all. That data should probably
>> reside in a single table. With a "notes" attribute, there really
>> might not be a need to hold further item-specific information for 80%
>> of the item types. Of course, I am probably over-simplifying
>> drastically here.
>
>The problem I have with "notes" is that "notes" are not data. Invariably,
>someone down the road asks, "why can't you just search the notes field?"
>If you make the mistake of trying to explain the difference between parsing
>natural language and scanning structured data, you are apt to be dismissed
>as a geek that doesn't understand people very well.

In my booklist database I have a "see-also" table that does something similar to that "notes" field but with marginally more structure, it uses EAV principles. The table has three fields. Two of these are foreign keys linked to the primary key of the Author table, the third is a free-text Notes field. The user (which is always me) can create a link between two entries in the Author table and describe it. I know several obvious reasons why I might want to do that and already have structures to record pseudonyms. This EAV structure records other relationships. What I will be able to do is to extract every unique value in that Notes filed and use it as a candidate for a new link-entity. That's what I would do before handing this database over to a customer. If I thought it necessary I might retain the see-also table so that other users could add new relationships as they discover them. By doing that they would be creating new candidates for link-entities to be incorporated into a revised structure.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.