First off, I consider myself very ignorant about databases so any attempt at lending a hand will only help me if you write very slowly to me. I have been struggling with many concepts about RDMS. I have been able to get through some of them, but absolutely stuck on others. I understand tables, attributes, keys, and join tables somewhat. I am having problems with modeling my solution though.

As I read up of some topics, such as specialization, generalization, subset tables, supertypes and subtypes, I am really lost. I believe I really want to learn and understand Supertypes and subtypes as I am seeing corollaries with what I visualize logically.

For example, I built a vendor database that tracked the different companies and the employees of the vendors. Then I built a database of our employees, then a database of our customers. After doing this and then reading some books, I was building tables based off of the Role these people or organizations "played". The books had suggested a Party concept and for the life of me I can not build a physical database using the book as a guide. I believe I am in the process of learning that data modeling can be at the logical level and will not represent the actual physical model directly. That is my problem. I have searched high and low to find an example of a physical database model of a Party concept. A model that says build these exact tables, use these exact keys and a functioning physical database is possible.

I think I also understand that the logical model can be physically implemented in dozens of ways and the business process and rules are a factor along with performance, etc. So I appreciate that there is no one way for all, and I appreciate that this is why a database design is a valued asset.

This is one example of the problem that is preventing me from moving forward to any degree. I have a similar problem with Equipment (as a Supertype) and specific equipment as subtypes.

I personally favor the concept of supertype and subtype tables because it is rather cut and dry when defining the tables fields - I think. I was first exposed to the subset table concept in the book, "Database Design for Mere Mortals", which I thought was good, but did not go into novice detail to help walk me completely through implementing it. So I bought Len Silverston's 2 books "Data Model Resource I & II" which used the terms Supertype and Subtype - and I can not wrap my head around it either. I thought Len's books were going to lay our in explicit detail the "physical" build, not the "logical" model. Due to my ignorance, I can not translate it into a manner that I can use. I continue to read them in hopes that the light bulb will turn on.

I do not even know how to ask a specific question about my troubles as my head is swirling. But to kick start any response, can anyone chime in about the use of Supertype and subtype entities IN a physical database?

Any help is appreciated.

have a look at this, there are some really excellent examples:

Supertypes and Subtypes PPT (with annoying transition sounds)

Thanks Rudy for the link.

When you mentioned the PPT had some annoying transition sounds, I thought it couldn't be that bad... you were right.

I do understand the concept as presented (to a reasonable degree). However, I am having a hard time figuring out which physical tables to actually build - and the keys to use in which to join them.

For example, in the PPT link you provided, can I build the physical tables exactly as they presented in the slides as entities? Or, are those logical models and during physical designed they are transformed and grouped into fewer tables than presented?

Any help appreciated.

as far as i can tell, yes, you would build the tables (the rectangles in the PPT diagrams) using those columns (the ovals attached to the rectangles)

I know, that can be a loaded question... I have attached a diagram showing what I am using as reference (the top portion of the diagram) and what I have built (bottom portion).

The top portion is a replication of a data model in a book by Len Silverston. I have struggled with interpreting the book's models which makes trying to build anything into a physical FileMaker set of tables and relationships very confusing.

The bottom portion of the posted diagram shows what I built and seems to be functioning - basically. The book presents the Party Classification as a Supertype, and the rounded rectangles inside of it as Subtypes of the Supertype.

However - in my struggles of trying to build something - I ended up with what you see in the bottom portion - but it is not in a supertype / subtype structure (I think).

Can anyone help clarify ANY of this for me. I believe that if I can get over some of the interpretation issues - then I can focus more on the difficulties I have in implementing it in the RDMS I am using.

Note how I "invented" the two tables - "Category" and "Subcategory" to provide a place to store those values, but they are outside of the Supertype.

I have no experience on the different methods one may choose from in how to implement this type of Supertype / subtype so any help is appreciated.

Thanks!

i'm afraid i can't offer too much help because i really don't know what you're trying to achieve

i have an idea

why don't try to load your tables with some non-trivial data

then show us the data and we'll see if your tables make sense


for one example, you can put your categories and subcategories into a single table

see Categories and Subcategories

Let me try to clarify...

I am trying to create a physical database using the data model I attached above (the upper portion of the diagram). The data model is one that came from "The Data Model Resource Book" by Len Silverston.

In his book, many of the models use Supertype/Subtype structures. I have never built a structure like this before - so I tried the best I could to interpret the model as presented in the book and my resulting effort is shown in the attached jpg as well.

Earlier in the thread, you had posted a link to a .ppt that explained Supertype and Subtype and provided further clarification that I could build the tables as represented in that .ppt link.

As I mentioned, I have built it (shown in the lower portion of the diagram), it does "seem" to work, and I did provide one line of "sample" data as shown in the .jpg in hopes that one could see what type of values were being held in the tables I built.

What I am trying to "see" or "get" is this: the Supertype/Subtype arrangements in the data model are prolific and I wanted to get some insight as to "how" and "why" those who are much more experienced than I would choose to design the actual table structures.

I did not build the tables NOR relationships in a Super/subtype manner as the model is shown, but I can see all of the relevant classification levels in the "Party Classification" table that joins a Party to a set of classification values.

For example, I could interpret the model "literally" and build 1 table for each of the subtypes as shown in the book's model and related them to the Supertype. But, I "saw" that maybe the values could be held like the structure I presented in my attempt. I am NOT trying to be ingenious - far from it. I am struggling with intrepretting the data models as presented in the book. If I take it literally and build 1 table for each Supertype, and 1 table for each subtype, and 1 table for each of its subtypes - that's a lot of tables. I have no problem spending the time and building all the tables - I just have no experience in this area.

Your link you provided about the values all being in one table is appreciated - thank you. I have done that in the past (with little "real" understanding) and have been pushing myself to build in a higher "normalized" fashion. I am aware of the denormalize for performance debate.

I have somewhat convinced myself that the Supertype/Subtype modeling method is the way I want to go as much as possible, in addition to normalizing my database as much as possible. Any help / comments are appreciated.

any chance you could populate a few rows of actual data into your tables and show that?

Okay - took a while and it is not perfect - see attached jpg. Many of the labels are over described but I think it should be clear to see what I am doing. Hopefully the sample data is enough to see where this is going.

Again, this is all about "how" to interpret the model from the book into actual tables and relationships. I am building the database and have over 100 tables so far, but structures and concepts such as super/sub have got me very interested in implementing it as I am sold on the concept.

My tables (I think) are normalized quite a bit (my interpretation) and I think many designers would suggest that I denormalize many of these tables - but I am resisting all denormalization.

Many of the critical connections are not connected, but the entity tables are pretty sound. I need to work on the "roles" model next and was trying to use my sample attached as a springboard on how I could tackle that model.

Any help is sincerely appreciated. Thanks!

I want to point out this:
In the book's model (see top portion of my first attachment) there are a total of nine Super/subtype structures in the Party Classification AND Party Types areas of the model.

I have built a total of 4 tables and they are not connected in the Super/Sub arrangement. Actually, I built two tables that did not exist in the model (Categories and Subcategories) and have them supply the Type table for its combined final "type" value. I am NOT saying I have found a better way - rather I am confused as if the book's model is suggesting I literally build it as presented in the model OR when actually implementing it into a physical it SHOULD be built differently - like extrpolate the meaning of the model into "x" structure. See my confusion? Or am I just confusing everyone else?

Can I get anyone's input? It would be much appreciated.

Well Rudy, any thoughts? I posted some sample data but have not heard a word yet.

if you are referring to the diagram in post #9, i'm sorry, i have failed completely to understand what you're doing

could you show the design of the tables using CREATE TABLE statements instead?

I am guessing the "Create Table" statement is a SQL term. I am not working in SQL. I thought I laid out what I was in need of help with. Maybe I over-explained it and no one wants to take the time to read it.

I'm afraid that does apply to me. Many of us are professionals (or at least employed as such) so can't spare the time to read huge threads & posts. I've read Rudy's responses and tried to glean what I can from those.

pootle flump ur codings are working excelent.