Table design

[bachgen]

Hello,

I am looking at creating a database for a charity, to help them maintain there volunteer information.

The database will need to hold information about the charity, volunteers and there roles, language and status.

This is the db design so far.

Branch
id PK - charity number
name *
house *
street1 *
street2
street3
town *
city
postcode *
p_office *
p_land *
p_mobile
fax
email not sure of data type and length
www not sure of data type and length

volunteer
id PK - unique number, auto number
vol_name *
f_name *
s_name *
house *
street1 *
street2
street3
town *
city
postcode *
p_home *
p_work
p_mobile
email
last_change date/time, when record last updated

vol_link_lan
vid FK, PK
lid FK, PK
level
qua

language
id PK, Auto
name *

vol_link_rol
id PK,
vid FK
rid FK
sdate *
edate *

role
id PK, auto
name *
description

vol_link_sta
id PK,
vid FK
sid FK
sdate *
edate *

status
id PK, auto
name *
description

I am not sure if the vol_link_rol and vol_link_sta are correct, I have created a PK to ensure there is a record of the volunteer and the status and the same for the roles table.

comments please.

 

[statsman]

Branch
Email - text - 50 should be more than sufficient
www - text - 50 should be more than sufficient

Volunteer
looks OK
You could set the last_update to the current date if you are using an edit form. takes care of it automatically

The remaining tables could all be part of the Volunteer table unless you have a special reason for their being separate. Example - Languages
Lang1
Fluency1
Lang2
Fluency2
etc.

 

[boblarson]

The remaining tables could all be part of the Volunteer table unless you have a special reason for their being separate. Example - Languages
Lang1
Fluency1
Lang2
Fluency2
etc.

This is an example of repeating fields and SHOULD be in their own table as shown first. statsman, this is a suggestion for non-normalization and is not good. The way he originally shows it, you can add as many languages and fluency information as a person would know. If a person knew 10 languages you could store that info. In your example here, you would only have a finite number and, while it could handle most people's languages and fluency, it is not real good database design.

 

[tehNellie]

I'm with Bob, I inherited a DB with 8 attibute fields hardcoded into a table, what happened the day after? I got something with 15 attributes. That and we've got loads of entities that only require a single attribute which leaves us with lots of columns with no data in it, and worse at point of inheritence a mix of ZLS and NULL.

Email - text - 50 should be more than sufficient

I'd double check this, I thought the same adding an email column to a table yesterday until I double checked the source data using LEN() and found lots that exceed that limit. If it's for a single organisation (so everything after @ is essentially static) then you only need to store the <firstname>.<lastname> data (or whatever format it is) and can add the @blah.com in code

 

[boblarson]

I'd double check this, I thought the same adding an email column to a table yesterday until I double checked the source data using LEN() and found lots that exceed that limit. If it's for a single organisation (so everything after @ is essentially static) then you only need to store the <firstname>.<lastname> data (or whatever format it is) and can add the @blah.com in code[/QUOTE]
Definitely! An email address is at maximum 255 characters, so why not just set the data type length to 255? You don't suffer the situation like SQL Server, as Access doesn't pad the field to fill out the remaining characters (which is what I had originally thought until Pat Hartman cleared that one up for me).

 

[tehNellie]

You don't suffer the situation like SQL Server, as Access doesn't pad the field to fill out the remaining characters

Varchar is your friend in sql server to avoid this. Char sets an "absolute" field length which is padded, varchar doesnt. But I digress.

 

[bachgen]

Replies

Thank you all for your input, can I take it that the role status and links tables are all ok.

Some of the roles have a default time period (4yrs) then the roles can be re-assigned or a new volunteer undertakes this role.

I am not sure if this should be contained within the database or code ?

 

[tehNellie]

I am not sure if this should be contained within the database or code ?

If it's possible, do both. From my perspective the Database is your last line of defense preventing corruption of your data, if it can be enforced in both Code and at a Database level, do it. If you're both the Coder and DB admin pretend you don't write the code anymore, do you trust that guy not to make a mistake and screw up your data?

 

[bachgen]

If it's possible, do both. From my perspective the Database is your last line of defense preventing corruption of your data, if it can be enforced in both Code and at a Database level, do it. If you're both the Coder and DB admin pretend you don't write the code anymore, do you trust that guy not to make a mistake and screw up your data?

To help achieve this I have added a period column in the roles table:

role
id PK, auto
name *
period
description

I have set this as a optional as some roles do not have a limited period. What are your thoughts has anyone got a better method of achieving this.

Regards

 

[bachgen]

Anyone with any thoughts on this ?

 

[bachgen]

Address table ?

I have been looking around and I have seen that some designs use a separate table for the address.

Why would this be done is this something I should incorporate.

Thanks.

 

[bachgen]

Address table ?

I have been looking around and I have seen that some designs use a separate table for the address.

Why would this be done is this something I should incorporate.

Thanks.

 

[The_Doc_Man]

You put separate tables for addresses in case someone or some organization moves but you want to keep records of where they were before their last move. OR... you have two persons involved from the same address who, for legal reasons, wish to be addressed separately.

In which case your address table has an "AddressID" and all tables that involve addresses just point to that particular table and ID.

This next is a fine point in normalization: I think that in this case, even though you might wish to invalidate an address, you would not make validity dates part of the key. Because the address in the table doesn't change due to being invalidated. Instead, the POINTER (Foreign Key) in the tables using that address would be updated.

A further wrinkle would be whether you need to know who USED to be at a given address, in which case you need to have a history table showing validity and applicability data for each address - as a separate table from the main address table. But that's only if you need such a history.

 

[CraigDolphin]

You put separate tables for addresses in case someone or some organization moves but you want to keep records of where they were before their last move. OR... you have two persons involved from the same address who, for legal reasons, wish to be addressed separately.

I also do this because mailing addresses may differ from physical or delivery addresses. When generating mailing lists you need one; when generating call-lists you need the other. Sometimes the mailing and physical addresses are one and the same so this avoids data redundancy which is what I thought normalization is supposed to avoid.

 

[boblarson]

I Sometimes the mailing and physical addresses are one and the same so this avoids data redundancy which is what I thought normalization is supposed to avoid.

I solve that one by having a mailing address within the same record and use a query to pull the address by using an IIF to put in the mailing list, if there is one, but to use the physical address if a mailing address isn't entered.

 

[CraigDolphin]

I thought about doing that too. But after reading about normalization on this forum, I thought I was doing the 'right thing' to have one address per record with an additional field storing information about what type of address it is. That said, if I'm going overboard then maybe I should drink less coffee and more beer Lord knows I'm still learning this normalization thing

I will say that doing it this way made it extremely easy to generate mailing lists or call lists from the same query using a parameter from a form. And it avoids printing labels for companies that have had no address entered, which might save a few trees and old horses (glue) over time.

 

[boblarson]

Actually, normalization doesn't mean you have to have a single record for everything. I forget the exact wording to explain this, but maybe The Doc Man will see this and chime in - I love his explanations, but essentially the gist of things is that you do not have to totally normalize everything. In fact, there ARE times when it becomes beneficial, either on a performance standpoint or a feasible based on resources standpoint, where denormalization can be okay.

Now, if ANY company could have multiple mailing addresses for your stuff, then that should be in it's own mailing address table. However, the result can still be the same as you can tie the two tables together with a query and only pull mailing addresses in if they exist and use the physical address if they don't. It's just a simple IIF or Switch statement that can pull this off.

 

[CraigDolphin]

Fair enough.

I guess the aversion I have to this stems from experiences with people who swear, with heartfelt sincerity, that there are only one or two possibilities to be stored, only to come back a couple months later with a third and fourth possibility. Using the 'more fields' approach means re-jigging tables, forms, and queries up the gazoo. Using the 'more categories/separate table' approach means less work for me when the light dawns on the user that there's another category out there that they helpfully neglected to inform me about.

An example of this is a database I'm doing for my father to track companies/contacts etc. At first it was solely aimed at generating mailing lists. Then a few weeks later he asked if it were possible to do 'call lists'. Then a few weeks later he mentioned needing to provide a 'delivery' address to his head office (different than the mailing/billing address and different than the physical address of where his contacts are located). Because I had set it up addresses with a separate table, it was no problem to deal with new 'kinds' of addresses as they arose.

Then again, maybe this is just the result of my own inexperience at asking the right questions during the scoping phase, or rather, my naive acceptance of the answers to the right questions. The more I do this, the more I understand what people mean when they say that figuring out your table relationships is the MOST important part of database development. Forms and all are nice shiny wrappers, but if your tables are turds then all you have is a nicely wrapped turd. No amount of re-wrapping can change the fundamental desirability of a turd.

But it's good to know that I can sometimes denormalize without being too embarrassed to show my face here And if Doc_Man chimes in again that's just great. I have learned a great deal from him (and you too Bob)

 

[boblarson]

You DO have a point about people coming back later and expecting something different that totally throws you out of whack. I don't think you can eliminate that completely, but it does require quite a bit of questioning and also, if you THINK it might be possible for them to do something I would just go ahead and plan for that possibility. However, in my case, I know my data well enough to know that I wouldn't need a separate table.