If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lost all sense of proportion
Hello All,
So in my ongoing attempt to create the most open and flexible charitable receipting database, I have created a convoluted table structure comprising 26 tables, half of which seem to be junction tables. I am wondering if someone would be able to look at my table structure and let me know if it makes any sense at all. I wonder if it helps that I used Allen Browne's Human Relationship database as a jump off point, but I seem to have lost all sense of proportion since then. Concurrently, I have a barrel of picky donors and pickier users, so it has been designed with all these needs in mind, I hope. If you're willing to take a look, please let me know with your email address. Many thanks, Tal. |
#2
|
|||
|
|||
Lost all sense of proportion
Hello Tal,
Assuming that this is talking about your thread of 1-2 days ago, I was followiing that and guessed that you might end up where you did. It involved you jumping to one of the more sophisticated (recursive) linking architectures and dealing with other "final stage" functionality when it looked like you still hadn't fully thought out the nature and organization of entities and information that you are databasing. Would suggest starting there. One point in particular is that you seemed to want to thinking about treating address as entities, but using an architecture that treated them as attributes of entities. |
#3
|
|||
|
|||
Lost all sense of proportion
H Fred,
Interesting things to think about. I think the reason I got where I did with the addresses is because they seem to have a life of their own - meaning one client can have multiple addresses and multiple clients can share one address and this seems to happen independent of phone numbers, email addresses, credit cards, etc. So I have created a whole bunch of little tables with a big junction table. Does this make sense? Thanks for your insight. Tal "Fred" wrote: Hello Tal, Assuming that this is talking about your thread of 1-2 days ago, I was followiing that and guessed that you might end up where you did. It involved you jumping to one of the more sophisticated (recursive) linking architectures and dealing with other "final stage" functionality when it looked like you still hadn't fully thought out the nature and organization of entities and information that you are databasing. Would suggest starting there. One point in particular is that you seemed to want to thinking about treating address as entities, but using an architecture that treated them as attributes of entities. |
#4
|
|||
|
|||
Lost all sense of proportion
Hello Tal,
You have to start with the nature of the information that you want to database and the missions that you want your database to accomplish. This will include the types of scenarios than you DB must accomodate. In many cases, extra allowing for scenerios has a cost of extra complexity. In my opinion, you'll want to ask yourself these questions: 1. Do you NEED to track households as an entity? I.E. if Dick and Jane are married and need to be tracked, is just tracking them as two individuals good enough, or do your need their household to be tracked as an entity? 2. For your organizations, is your data element an instance of the organization at a location, or is it the organization as a whole? (the former is simpler) 3. Is it good enough to just record individuals as a member of ONE organization (Business, Club, Household) #1 and #2 affect the answer to #3. If the answer to #3 is "Yes" then a simpler 2 level structure where the main tables are "Organizations" and "People" would work.. If the answer to #3 is "no" then you need somethiing more sophisticated / complex / abstract like Allens discussed database structure. 4. Is it good enough to just record the "main" address for each organization and person? (#2 will affect the answer to this) If "Yes", then addresses should be just fields in that/those tables(s). If not, then addresses should be in separate tables linked to those entities. There's my two cents. Not sure if it helps or not. Fred |
#5
|
|||
|
|||
Lost all sense of proportion
Hi Fred,
Of course this is very helpful. Unfortunately the answer to most of your questions is No. Thus my need for complexity. I will give you an example: Larry is a donor. Larry sometimes donates as an individual. Larry sometimes donates with his wife - ie. household comprising several individuals Larry sometimes donates through his business - another corporate entity comprising several different individuals Larry sometimes donates with his brother through their family foundation - yet another different corporate entity comprising another set of individuals. Sometimes Larry's wife donates with her brother through their family foundation. And the kicker is this: Larry sometimes donates with his wife but wants the receipt to go through his business or vice versa. Larry's wife sometimes donates through her family foundation but wants the receipt to go to Larry's business. Or sometimes Larry's family foundation donates but want the receipt to be split between the 3 siblings. See what I'm working with? Thoughts greatly appreciated. Also, Fred, if you're willing, I would be very happy to send you the Access file with the table structure and Relationships. Cheers, Tal "Fred" wrote: Hello Tal, You have to start with the nature of the information that you want to database and the missions that you want your database to accomplish. This will include the types of scenarios than you DB must accomodate. In many cases, extra allowing for scenerios has a cost of extra complexity. In my opinion, you'll want to ask yourself these questions: 1. Do you NEED to track households as an entity? I.E. if Dick and Jane are married and need to be tracked, is just tracking them as two individuals good enough, or do your need their household to be tracked as an entity? 2. For your organizations, is your data element an instance of the organization at a location, or is it the organization as a whole? (the former is simpler) 3. Is it good enough to just record individuals as a member of ONE organization (Business, Club, Household) #1 and #2 affect the answer to #3. If the answer to #3 is "Yes" then a simpler 2 level structure where the main tables are "Organizations" and "People" would work.. If the answer to #3 is "no" then you need somethiing more sophisticated / complex / abstract like Allens discussed database structure. 4. Is it good enough to just record the "main" address for each organization and person? (#2 will affect the answer to this) If "Yes", then addresses should be just fields in that/those tables(s). If not, then addresses should be in separate tables linked to those entities. There's my two cents. Not sure if it helps or not. Fred |
#6
|
|||
|
|||
Lost all sense of proportion
Although seeing your DB would provide additional info, I think that what's in
this thread is more useful to your solution. If you don't have a clear picture of these underlying things, then your DB structure is built on swampland. So, we know that there are myriad possibilities of where donations can come from. That still doesn't finalize the answer of whether each one of those possibilities needs to be tracked / recorded as an entity. Now you have to decided (for yourself or to tell us) what exactly it is your mission to tack in the DB. Obviously (and vaguely speaking) it includes contact informaiton on organizations and people. Is it also your mission to track DONATIONS? And if so, have you decided that a donation has to be recorded as having come from a single entity (as you are sort of implying). So, if Doug and Tom decide to get together and make a donation, you would have to create "DougTom" as a new single entity to tie it to. Or could you / would yo prefer to just record the donation as being 50% form Doug and 50% from Tom. (which would simplify your life) If your answer is the complex variant of all of these things, then you need a structure like Allens and your life is going to be complicated. I'd make those 5 tables, not 30. Maybe add one for addresses. Sincerely, Fred |
Thread Tools | |
Display Modes | |
|
|