I have been desiging databases (small ones) for probably 2 or 3 years now, and I still don't know what I'm doing.

Would someone mind taking a look at schema and let me know if I'm on the right track?

Idea is that I wanted a unique identifier for Persons, Organizations (and any other groups that may come along such as "Schools", "Agencies", whatever) and so created the Identifiers table.

Then, any Identifier will make a transaction (not literally, this is just for record keeping, not real-time transactions) to another Identifier, which may or may not be related to an Initiative/Program.

http://216.65.101.25/upload/uploads/sm_schema1.gif

Of particular concern to me is the Identifiers_1 table that Access automatically generated when I tried to link IdentifierID to more than one field in the FundingTransactions table. Is this right, or is it doing something funky? I'm not sure what it should look like in the Relationships window in Access, but in my schema, IdentifierID from the Identifiers table should have link to both FundingSourceID and FundingRecipientID (make sense? =)

Just want to know if I'm going wrong anywhere, too often I discover later in the game that a certain schema would have suited my project better.

Thanks for looking!

What you really need is a supertype/subtype hierarchy.

Strangely enough I was working on something like that today.

Here it is. Programatically you resolve the subtypes.

Thanks for your reply certus.

If I understand correctly, you have a table for Personal_Customer which determines which Persons are Customers, as well as a Commercial_Customer which determines which "Companies" are Customers, and then the Customer_Contact table determines which Persons belong to those companies?

I just want to be sure I'm clear on that.

Now a problem I have in my database is that the FundingTransactions table records a transaction of money from one source to another (Person to Organization, Organization to Organization, etc.), and as in your database you have Customer to uniquely identify each Customer (Personal or Commercial), I have the Identifier table (maybe better to rename this?).

Would I be better off creating two tables named Donor and Recipient to fit in between the relationship of Identifiers and FundingTransactions? (instead of Identifiers having a direct relationship to FundingTransactions, there would be a Donor and Recipent table for the respective relationships). I know it would work, I just question whether I need the extra two tables or not, maybe I do?

Anyway, sorry if I went off on a tangent, I'm starting to better understand this whole relationship heirarchy thing, more so since I started doing a lot more OOP. I'm just foggy on a few things.

Anyone at all? =)
I'm still kinda stuck at this point, not sure exactly where to go. After some searching around it doesn't seem logical to create a "donors" and "recipients" table, but I think I'm still not fully understanding how to implement a Supertype/Subtype heirarchy into my model.

Thanks pros!

First, you only need one Transaction table with two relationships from the "customers" table.

Second, the supertype tables have a "type" that determines which subtype to use for a particular relationship. The relationship is one to one between supertypes and subtypes. For example:

Person ID = 1234
Person Type = "Personal Customer"

You would know to programatically create a subtype instance in the Personal Customer table. You would also generate a Customer instance at the same time. You will have to write code to enforce the supertype/subtype structure.

Hi certus,
Thanks again for your reply.

This may seem like a silly question, but if you have a one-to-one relationship between Customer and Personal_Customer, isn't the sub/super type implied by the ID, since the ID is unique across all sub types?

I don't mean to question your design (honest!) I am just still trying to fully understand the super/sub type relationships.

Thanks!

Person has two subtype entities: Personal_Customer and Customer_Contact. The person_type tells your program which subtype entity contains the subtype instance. The correct instance in the subtype is determined by the person_id.

Hi certus,
I think that makes sense now, in other words the database is (obviously) smart enough to determine which subtype is required from the tables (by doing an inner join in my query) but having the person_type is more for my actual application, to be able to determine which type of object to instantiate?

Hopefully I'm understanding correctly, thanks for your help!

The person_type tells your application which table contains the related instance. For example: if the person_type is "Personal_Customer" you know that the Personal_Customer table contains the subtype instance. The next step is the person_id tells your application the specific instance. For example the application would know to create an inner join between the Person.id and the Personal_Customer.Person_id.

Excellent, that makes a lot of sense now. I never really thought of doing that, but now I know better. Thanks for all your help. I will post later with more questions I"m sure, but I'll take this and expand on it for now.

Thanks!