Developing Access DB - Am I going about things the right way? (1 Viewer)

[Atrias]

Hey Everybody!

It's been a long time since I wrote an introductory message and due to my work duties becoming focussed on some other areas I forgot all about this forum and the resources it could provide me. So sorry for my absence and forgive my impertinence in coming to you with all of this!

As I am now coming to construct what is essentially my first "proper" Access database I really wanted some guidance to make sure I am on the right lines before I get too far in the development process. Apologies in advance for what will inevitably be a long-winded post; I'd rather give as much information now than having to fill in bits as I go along.

Background

The company I work for is a non-profit association of insolvency \ bankruptcy \ turnaround management companies. One of their primary duties is to facilitate the running of various conferences and seminars throughout the world during the course of each year. There is generally one major annual conference and 4-5 smaller seminars.

Prior to being hired by the associations, all IT work was either outsourced or cobbled together by members of staff with no massive degree of technical expertise - as mentioned, we're non-profit so everything has to be developed as a cost-effective and needs-must basis.

As a result, the database that I have been asked to re-write is archaic at best, and designed without much thought for standard database design principles. (And to make matters worse when it comes to making use of any of the pre-existing data, the database was written in Access 97!)

Aim

So, having said all that. I am trying to design a database that will allow us to store information about the conferences \ seminars (hereafter referred to as event for ease), the registered delegates, the sessions at the event that they have requested to attend, any social functions they wish to attend and also to record financial information for invoice and auditing purposes.

My intention is to have a DB that will be usable from event to event, eliminating the need for multiple databases. I would certainly want to split the database to allow this so that front-end objects are separate from back-end objects but a comprehensive design of this nature might be beyond my capabilities at this point.

I have developed a number of tables and fields - attempting to normalise as best as possible - and have reached the point where I am looking to define relationships for the tables but as stated above, want to make sure I'm doing the right thing so far before I get ahead of myself. I'm confident that once the underlying data structure is established that creating the necessary forms and reports won't be too much trouble.

So far...

I have established the following tables and fields:

tblBookingInfo
BookingID - PK
DelegateID - FK
EventID - FK
BookingDate
BookedBy
BookingStatus
PaymentMethod
BadgeName
AccompanyingPerson
HotelInformation
DietaryRequirements
Remarks

Comment 1: Not sure if I am right in including 3 ID fields (AutoNumbered in the relevant tables below).

Comment 2: BookedBy has been implemented thus far as a lookup field populated by information in "tblEmployeeInfo" below. As per your commandments I fear this is already incorrect?

Comment 3: Added PaymentMethod and linked it to tblPaymentMethod in the same way as above.

tblBreakoutFuncInfo - Omitted at this time as I am not entirely sure of the best implementation or if this is necessary but this table would list the different breakout sessions (A1, A2, A3 ... B1, B2, B3 and so on for example) and Social Functions for each event so that this information can be attributed to a booking. Possibly would require two separate tables to differentiate Breakout designations from Social Functions?

tblCompanyInfo
CompanyID - PK
CompanyName
CompanyAddress1
CompanyAddress2
CompanyTownCity
CompanyStateProvince
CompanyZipPostalcode
CompanyCountry
CompanyPhone
CompanyFax
CompanyEmail
IsG36?

Comment 1: IsG36 is a Yes\No value which would be used to signify whether a company is a member of the Group of Thirty-Six.

Comment 2: Am I right in using Address1 \ Address2 fields or should this be avoided? I query this as being an international association there is no one fixed address style that we would be able to use.

tblDelegateInfo
DelegateID - PK
CompanyID - FK
DelegateTitle
DelegateFirstName
DelegateInitials
DelegateLastName
DelegateSuffix
DelegatePhone
DelegateFax
DelegateEmail
IsMember?

Comment 1: Again, "IsMember?" is a Yes\No field to signify whether the registered delegate is in-fact a member of our association because we also have delegates who are not necessarily members. This is an entirely separate matter to "IsG36?".

Comment 2: Again, concerns about the address format are as above

tblEmployeeInfo
EmployeeID - PK
EmployeeFName
EmployeeLName
EmployeeInitials

Comment 1: I initially thought to use tblEmployeeInfo as means to populate "BookedBy" in tblBookingInfo Possibly wrong, as I notice your commandments recommend the avoidance of lookup tables?

tblEventInfo
EventID
EventName
EventStartDate
EventEndDate
EventIsSeminar?
EventVenue
EventAddress1
EventAddress2
EventTownCity
EventStateProvince
EventZIPPostcode
EventCountry
EarlyMemberFee
EarlyNonMemberFee
LateMemberFee
LateNonMemberFee

tblPaymentMethod
PaymentID
PaymentMethod


Relationships

Now, as far as relationships go the following facts can be said to be true:

- An Event can have one or more Delegates
- An Event can have one or more Bookings
- A Delegate can attend one or more Events
- A Delegate can only have one Booking per Event
- A Delegate can belong to only one Company
- A Booking can be made by only one Employee

So, what I'd really like to know is, am I doing anything fundamentally wrong at this point or anything that is likely to cause me problems further down the line?

I've read that Many-to-Many relationships are not very common but based upon the above it looks like I'd end up with a few Many-to-Many relationships. tblBookingInfo looks to provide the link to-and-from a number of other tables, is this OK or is there a better way of doing this?

To anyone who has read thus far, my thanks and appreciation in advance for any assistance you might be able to provide. Also, given the magnitude of the project there will probably be things that I've missed out or haven't clarified so please let me know and I'll fill in any blanks!

 

[spikepl]

In general:

1. Keep object/field names SHOOOOOOOOOOOOOORT!
2. If you attach INFO to almost all tables, then the word INFO does not add value and is redundant
3. A table holds multiples of whatever it is it holds. Hence it would be, customarily, named using the plural. So a table holding employee data would be tblEmlpoyees

4. Keep your questions concise: more likelihood of answer.

So, having said all that. I am trying to design a database that will allow us to store information about the conferences \ seminars (hereafter referred to as event for ease), the registered delegates, the sessions at the event that they have requested to attend, any social functions they wish to attend and also to record financial information for invoice and auditing purposes.

You forgot to specifically mention the purpose of the db. "store data" is normally just a station towards ...what exactly, in your case? List the functions expected fulfilled using the db - its raison d'etre!

tblBookingInfo
   BookingID - PK
   DelegateID - FK
   EventID - FK
   BookingDate
   BookedBy
   BookingStatus
   PaymentMethod
   BadgeName
   AccompanyingPerson
   HotelInformation
   DietaryRequirements
   Remarks

Comment 1: Not sure if I am right in including 3 ID fields (AutoNumbered in the relevant tables below).

This implies one booking per one delegate - is that true? One would exeoct poissibility of a booking including more than one delegate.

Comment 2: BookedBy has been implemented thus far as a lookup field populated by information in "tblEmployeeInfo" below. As per your commandments I fear this is already incorrect?

Yes that is incorrect. Remove any lookup fields in tables

Comment 3: Added PaymentMethod and linked it to tblPaymentMethod in the same way as above.

PayemntMethodID. Do not use lookup fields. Name fieds for what they truly hold.

tblBreakoutFuncInfo - Omitted at this time as I am not entirely sure of the best implementation or if this is necessary but this table would list the different breakout sessions (A1, A2, A3 ... B1, B2, B3 and so on for example) and Social Functions for each event so that this information can be attributed to a booking. Possibly would require two separate tables to differentiate Breakout designations from Social Functions?

Don't expect us to read minds and comment on things we have no idea about. If you want comments then do the deed, present it and submit yourself to our abuse.

tblCompanyInfo
   CompanyID - PK
   CompanyName
   CompanyAddress1
   CompanyAddress2
   CompanyTownCity
   CompanyStateProvince
   CompanyZipPostalcode
   CompanyCountry
   CompanyPhone
   CompanyFax
   CompanyEmail
   IsG36?

Comment 1: IsG36 is a Yes\No value which would be used to signify whether a company is a member of the Group of Thirty-Six.

So what?

Comment 2: Am I right in using Address1 \ Address2 fields or should this be avoided? I query this as being an international association there is no one fixed address style that we would be able to use.

Huh? Especially UK adresses can be a total pain, sometimes even requiring Address3. Do not understand you concern and do not understand your suggested alternative.

tblDelegateInfo
   DelegateID - PK
   CompanyID - FK
   DelegateTitle
   DelegateFirstName
   DelegateInitials
   DelegateLastName
   DelegateSuffix
   DelegatePhone
   DelegateFax
   DelegateEmail
   IsMember?

Comment 1: Again, "IsMember?" is a Yes\No field to signify whether the registered delegate is in-fact a member of our association because we also have delegates who are not necessarily members. This is an entirely separate matter to "IsG36?".

So what?

Comment 2: Again, concerns about the address format are as above

What concerns? There is no address mentioned in your table.

tblEmployeeInfo  
   EmployeeID - PK
   EmployeeFName
   EmployeeLName
   EmployeeInitials
   
Comment 1: I initially thought to use tblEmployeeInfo as means to populate "BookedBy" in tblBookingInfo  Possibly wrong, as I notice your commandments recommend the avoidance of lookup tables?

Don't mix lookups ( a presentation issue, or it should be) with data normalization issue. This table is fine.

So, what I'd really like to know is, am I doing anything fundamentally wrong at this point or anything that is likely to cause me problems further down the line?

Hard to say since you haven't done much yet. Go through the comments. When presenting your data structure, take a screenshot of the Relations page and attach it (you may need to zip it first, with post count <10). Tidy up the tables there (thier locations to aavoid too many crisscrossing relation lines) and make sure they are all fully expanded and show all fields.

I've read that Many-to-Many relationships are not very common

Huh? Rain and bad weather are not very common either.

tblBookingInfo looks to provide the link to-and-from a number of other tables, is this OK or is there a better way of doing this?

No idea. Clean up your db as per comments, present the data structure and supply the story explaning how the business works - who books what for whom.