Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25

    Red face Small Schema, Someone Crit?

    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  3. #3
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25
    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.
    --

    Travis Nelson
    http://www.travis-nelson.com/

  4. #4
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25
    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  6. #6
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25
    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.
    Last edited by certus; 02-11-05 at 12:06.

  8. #8
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25
    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

  9. #9
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  10. #10
    Join Date
    Jan 2004
    Location
    Florida
    Posts
    25
    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!
    --

    Travis Nelson
    http://www.travis-nelson.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •