New database design with ganotedp's party structure

[ESquared]

ganotedp posted the following extremly interesting and helpful URL in Thread669-799932 :

http://www.tdan.com/i021ht04.htm

.

Now I'm working at home in Access 2000 on a finance/contact info/web info database with these new ideas and understandings.

I have mostly been going lickety split and answering my own questions as they arise, but I've started to slow down with some uncertanties, so I'm once again hoping some generous readers might chip in!

I have several ideas for how to actually implement Locators.

- LocatorType, LocatorSubType, and I was starting to add LocatorSuperType. The Locator table would point to the smallest of these (subtype) and each of those tables would point to the one above it for a hierarchy. For example:

LocatorTypes: results of query joining 3 tables together
[tt]SuperL Locator SubL
Physical Address Address1
Physical Address City
Physical Address State
Physical Geo Latitude
Physical Geo Longitude
Electronic Phone Land
Electronic Phone Mobile
Electronic Messenger Yahoo
Electronic Messenger AIM[/tt]


- Then I thought, well, gee, what if I want more levels? Will I have to have a SubSubLocatorType and a SuperSuperLocatorType? Why not collapse them all into one table with a Parent field? I wrote a function to walk the tree upwards and return a name in my tables so I can make sense of them in raw format or if entering data in datasheet & subdatasheet view, e.g. "Electronic: Phone: Land."

LocatorTypes
[tt]ID Name Parent CanBeSelected
1 Physical (Null)
2 Electronic (Null)
3 Address 1
4 Geo 1
5 Phone 2
6 Messenger 2
7 Address1 3 True
8 City 3 True
9 State 3 True
10 Latitude 4 True
11 Longitude 4 True
12 Land 5 True
13 Mobile 5 True
14 Yahoo 6 True
15 AIM 6 True[/tt]


-This was pleasing me so far, but then I started working on an Attributes table and came up with an additional problem.

Aside: In the web page referenced above, this is called "External Party Identifier" but only has the "is identified by" field. (I dont fully understand the "identifies" link. I, a party, am identified by my Social Security Number, but how is an identifier identified by a party? Isn't is the same relationship?)

So, anyway, I want to store attributes about parties. For example, company size, height, weight, number of fingers, number of buildings (numbers, single or long or other)? Yearly earnings, Pay rate (currency)? Eye color, skin color, slogan (text)?

Plus, are locators and attributes something that could be combined into one table? Locator could be just another kind of attribute with subattributes and sub-sub-attributes.

But... I can't put these all in one table. Not if they are different data types. So, do I have to have a separate table for each data type, and make the top or bottom hierarchy of my attributes be Number, Currency, and Text? Or make a new field in AttributeTypes called DataType?
Then join separate tables to this to store the actual values?

[tt]AttributeLongs
AttributeDoubles
AttributeCurrencies
AttributeTextValues
...[/tt]

[tt]Attributes
Metrics: Eye Color, Type:Text
Peculiars: Favorite Color, Type:Text
Metrics: Eye Count, Type:Integer
Peculiars: IsVegetarian, Type:Boolean[/tt]

I certainly don't want to make a table with fields like:

[tt]AttributeID LongValue TextValue BooleanValue[/b][/tt]

and put a value only in the one that is correct.

- There's no Variant type in Access so I can't use that.

- I could store all the values in a text field and convert them on use (yuck).

Right now it's looking like the only thing to do is have separate tables for each data type.

Anyone want to help... not just with my specific questions, but also if you see errors or assumptions in my thinking that you'd like to point out!

I'd be happy to give you a copy of my empty database so far if you want to inspect it.

-E²

 

[ESquared]

This is complicated.

Should an account be a party? Consider a checking account. It has identifiers, it can "own" money or transactions. It can have relationships to other parties: Person-Account holder, Company-Bank provider, another Account-overdraft protection providor.

How about online accounts? Tek-Tips® Forums is a company that provides an account which I own. The locator of type Electronic:URL is

http://www.tek-tips.com,

for both the account and the company.

So now what about my username and password? Are they locators or identifiers or attributes? The author of the article put party name with party, but that's really an attribute. If I include accounts, some of them don't properly have names, they have account identifiers such as Checking, Savings, Credit Card, Web account, online game account, etc.

Social Security Numbers aren't properly an attribute of a person, they are actually an account# held by a person and provided by the government. What if someone gets ahold of my database and wants to manage two social security numbers... does he have to enter two people?

Locators are not in a many-to-one relationship with parties, they are in a many-to-many relationship, requiring a PartyToLocator table. Many people can live at the same address, and a person can be assigned more than one address (home, work, summer house).

So what about attributes? I originally thought of attributes as a one-to-many relationship: each attribute can only be owned by one party. But, if I homogenize Locators and Attributes into "information" then multiple people can be connected to the same attribute, requiring a many-to-many relationship. Does this work? When I add a new person as a party and wish to put her eye color, do I choose the same Metrics:Eye Color:Blue as I did for other people?

That seems okay for generic information, and allows me to update Blue if I realize I spelled it wrong or had it confused with Green... by changing it all people with that color will change. I can set start dates and end dates on the information in the PartyToInformation table.

But what about specific information such as account number and password? What if I use the same password at two sites so I connect two Account parties to the same password, but then I change the password at one site? I must add a new party, I cannot change the password or it will affect both sites. Is this covered by the start date and end date thing? My rule is to never change a piece of information unless I want it to cascade into ALL parties?

Can I add some business rules tables that govern what kind of updates can be done to specific types of infobits, and to what types of parties each apply? Eye color can only be applied to persons and animals. Slogan (I hope) can only be applied to companies.

If my friend gets his house surveyed and changes his house number (without moving), do I update the locator, or put an end date on it and create a new one with the new information? If he moves, it's obvious I create a new one. But the location did not change, the address actually did.

Maybe all these questions are obvious to everyone else... maybe people aren't interested. I know I tend to run on a bit.

But I sure was having fun today working on this.

-E²

 

[ESquared]

I must add a new party, I cannot change the password or it will affect both sites. Is this covered by the start date and end date thing?"

Cross out party. Replace with "infobit" or whatever makes sense as a single unit of information.

 

[ganotedp]

Well, I'm pleased that the article was thought-provoking. I've been mulling those notions over for years.

Data models run the spectrum from concrete to abstract: from the concrete "Customer" to the abstract "Party". In my mind, one goal of data modeling is to select an reasonable point on the spectrum that satisfies the business requirements, performs well, and is flexible/reuseable.

As others have pointed out (David Hay, Tom Kyte, et al), a generic model can be flexible, but dog-slow if there are lots of records, and hard for those who inherit the maintenance responsibility to understand. However, it's sometime unavoidable (for example, building a generic taxonomy data model).

A concrete representation like Customer, Vendor, etc often requires a bunch of cross-reference tables and you end up with a data model that sprawls so much that it's hard to grasp (I'm thinking of the data structures underlying some ERP systems).

There are various techniques to bridge the gap; for one project, we developed several views on the Party table that the developers use (they built the software model independently and much more concretely than I built data model).

Good luck and good modeling!

 

[ESquared]

The main problem with concrete representations is when one thing can behave as more than one representation. What if a company is a Customer AND a Vendor?

For example, when I worked at Philips Electronics, it was quite amusing to know that we "sold" CD loader mechanisms to other companies who built CD-ROM drives out of them and then "sold" them back to us. We were OEMing products that our OEM was partly OEMing from us...