Normalization: 'Entities' table, one-to-zero-or-many considerations 3

[ESquared]

I have a small database I designed and I am experiencing some difficulties. I'm hoping some generous readers might give me some advice. Design environment is Access 97 but that's mostly irrelevant... I might run this on, say, an MSSQL backend some day.

The easiest way to explain this might be to give examples. Here is some imaginary data in my current format:

[tt]Entities
ID FullName Type Parent
1 MyCompany 1 1
2 TheRed, Erik 2 1
3 ZZ Svcs 3 3
4 OftenWrong, Bob 2 3
5 Affiliate Co. 1 1

EntityTypes
ID Name CanBeParent TableSource
1 Company True Companies
2 User False Users
3 Partner True Partners

Users
ID Entity FirstName LastName Password
1 2 Erik TheRed *****
2 4 Bob OftenWrong *****

Partners
ID Entity Name Rating
1 3 ZZ Svcs 1450[/tt]

There is also a Vendors table I left out.

My design considerations:
1) I need to be able to assign a project to either a user or a partner, and conceivably to a vendor as well. So, I put these different types of things into an Entities table together. Additionally, I must know which users belong to which parent entity. Some entity types can be parents, others cannot.
2) Different kinds of entities have different information. A user has a first name, a last name, and a password, while a vendor does not. I do not want to store this information in the Entities table, it belongs in a separate table for each unique entity type. If I did, there would be many blank fields not to mention the difficulty of creating a form that handled each type properly.
3) I want to be able to create new Entity types or add fields for each Entity at will, with the minimum of work to forms and tables to accomodate. My Entity management form already uses the TableSource field to dynamically switch to the correct subform, so adding a new Entity type would mean creating one new subform and all my existing code would work. I have been careful in my queries so that new entity types will handled intelligently (for example, using fields like CanBeParent, or deciding whether to use inclusion or exclusion logic: "WHERE EntityType=2" vs. "WHERE EntityType Not In (1,3,4)").

While I solved many problems with this approach, I also generated some:

Do I store an entities name in the Entities table or in the table specific to that entity? I chose the non-normalized approach of storing a non-editable FullName in the Entities table and letting the type-specific tables organize the information according to its own needs. Users have a first name and last name, partners do not. Any edits to a user must trigger an update in the Entities table to maintain data integrity.

Maybe this approach was wrong... maybe I should eliminate the name from the Entities table to not store the same information twice. But then, how do I display a list of all Entities with their names using a simple query?

- I could do inner joins for each type and union the results together. This is messy and if I ever add a new Entity type I will have to modify the queries.
- I could do left joins and have the name be a calculated field concatenating the names together, nulls would be converted to empty strings. Same problem as previous.
- I don't think the contents of a record can be used to specify a join table. If so, I could use my TableSource field for this purpose.
- I can't think of another way.

I have seen some web references to this sort of One-To-Zero-Or-Many relationship, but no detail about practical usage considerations.

Would anyone care to comment on what I've mentioned already, or make suggestions or observations about things I haven't even considered yet?

I have used this Entities layout in the past and I expect to use it again in the future and I would really appreciate some help in working out the details!

Thank you,

E²

 

[harebrain]

The first thing you ought to do is reconsider using the word entity as a name for an entity or anything else. You're going to get sooo wrapped-around-the-axle trying to communicate with database-savvy people.

 

[ESquared]

Thanks, harebrain. What would you suggest for another name?

Just because *everyone else* assigns a certain meaning to a word doesn't mean *I* have to use it the same way!!! (Well... um... if I want to communicate with people I might consider it... )

Beings
Bodies
Individuals
Orgs
OrgsAndPeople
Objects
Parts
Persons
PseudoPeople
Stuff
Things
Units

Nothing good is coming to mind. The one I like best so far is Beings, but...

 

[harebrain]

Do you mean the entities entity, the entity column, or another entity entirely?

Sheesh, sounds like something out of Monty Python.

I don't know... add "actor" to your list, then consult a thesaurus and see if anything strikes your fancy. Avoid "object" as well.

 

[r937]

i personally like stuff and things

and let's not forget widgets, doodads, thingies, gizmos, gadgets, dinguses, gewgaws, knickknacks, whatnots, bric-a-bracs, folderols, jiggers, doohickeys, gimmicks, dingbats, thingamajigs, thingamabobs, thingums, contraptions, whatchamacallits, and whatsits

but yeah, stay away from entity and object



rudy

http://r937.com/

SQL Consulting

 

[ESquared]

Everyone has been SOOOOO helpful I'm giving them super-gigantic-extradimensional-energy-rift invisible stars instead of dinky purple ones.

 

[ESquared]

Oh, that *was* with help from a thesaurus.

 

[r937]

you have used this entities layout in the past and plan on doing so again in the future

so you have already figured out what it takes to keep the names fields synchronized from all entity type tables to the entity table

i did not want to comment on your situation with adding a new entity because i have no idea what a subform is

and actually, entity is an excellent name for this purpose



rudy

http://r937.com/

SQL Consulting

 

[ESquared]

Well, in the past I did not put the name field into the separate tables because there was enough uniformity between the types. Previous versions at other companies had the users as distinct, and all the entity-types were companies or company-like. So, there was no field that I chose to put into both tables.

Also, I am more experienced now and realize that some of the things I did in the past were clumsy... I'm hoping to find a better way.

 

[r937]

what you have is a typical supertype/subtype data model

how many fields are really needed for proper handling of a person's name? forename, surname, middle name, middle initials, salutation, honorifics, and i forgot a couple

company name, division name, location

seems to me like the name fields vary, so they belong in the entity type tables

if it were my decision, and i had to produce a list of all entity names, i'd probably just run a union query with each subselect doing a customized concat, and then yes, add another subselect when i create a new entity type





rudy

http://r937.com/

SQL Consulting

 

[ganotedp]

The generally accepted term for the entity you're discussing is "Party", which is a data structure/pattern in a "universal" data model. You can read about it at

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

Party is used by the United Nation's EDI standard (EDIFACT). You can read about EDIFACT at

http://www.unece.org/trade/untdid

. See for example the "party identification" structure at

http://www.unece.org/trade/edifact/composit/c082.htm

Party has also been carried into the ebXML standards, and there are several entertaining discussions about those. I particularly enjoyed those by William J. Kammerer (for example, at

http://lists.ebxml.org/archives/ebxml-transport/200012/msg00200.html).

Books about general data models have been authored by David Hay and by Len Silverston, et al.

 

[ESquared]

Thanks, ganotedp, great info!

I've already seen some ways to redesign my model. I'm not sure if I'll implement them this go-around, but I definitely will be thinking thinking thinking.

I'm still open to comments about the specific problems I mentioned above! (hint hint)

 

[ChrisHunt]

Seems to me you've got two possible lines of attack:
[ol]
[li]Create a single, generalised "party" table, with all the fields relevant to each type of party - accepting that they will be redundant in many cases.[/li]
[li]Create several tables - one for each type of party - and create a read-only view (that's a "query" in access parlance) to pull them all together.[/li]
[/ol]
Whatever happens, you don't want to be maintaning the same data in multiple places. The view/query would be easy to set up, I forget the Access syntax, but in Oracle it would be:

CREATE VIEW parties (name,type,id) AS
SELECT name,1,id
FROM   companies
UNION ALL
SELECT lastname||', '||firstname,2,id
FROM   users
UNION ALL
SELECT name,3,id
FROM   partners

-- Chris Hunt

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[ESquared]

I think I will remove the names from my main Entities/Parties table, and use the union query.

To accomodate my interest in doing as little work as possible to maintain and update my database, I will add a new field to my EntityTypes table that has the select statement in it to retrieve a properly formatted name. Then using VB I'll build my UNION queries on the fly from the entries in this table.

This way my database will continue to remain data-driven and not code-driven, which is one of my overarching and high priority goals.

For what it's worth, I have been doing this in many ways with my database: Creating extra tables to tell it how to behave, instead of hard-coding. I have a DatabaseSettings table with all sorts of parameters that guide operation (application name, registry location, default values of certain things). This has the side-effect of allowing me to make many changes to my front-end's behavior, without updating it. I think that my main control panel will be table-driven soon, as well. Unfortunately, Access doesn't let you create controls on the fly except in design mode: I'll have to either do a quick-switch-change at runtime; or place a bunch of invisible controls on the form to reveal and move around at will; or have a sort of wizard that creates the form for me from my data settings but would require an update to the front-end each time I changed something.

I'm enjoying myself immensely!

 

[r937]

... add a new field to my EntityTypes table that has the select statement in it to retrieve a properly formatted name

that's an excellent idea

you could also define a view column which pre-concatenates the name fields

to me, views are better than extra "meta" tables or columns mixed in with actual data

rudy

http://r937.com/

SQL Consulting

 

[ESquared]

I know what a view is, but what's a "view column?

 

[r937]

create view Vusers
as
select coalesce(Lastname,'')  
     + coalesce(', '+FirstName ,'')
          as FullName
     , *
  from users

create similar views for the other entity types

(check your dbms for concatenation operator)

then get your list of fullnames like this:

select FullName
  from Vusers   
union all
select FullName
  from Vpartners   
union all  
select FullName
  from Vvendors   
order 
    by Fullname

rudy

http://r937.com/

SQL Consulting

 

[ESquared]

r937,

Oh. I could have done *that*. I was just thrown off by the terminology.

I gave you a star, anyway, for giving me so much of your time.