Table Structure and Relationships for a Database Design
Hi all. I'm 'slightly more experienced than beginner' at database design and I have been asked by a beginner friend to help her design an Access database table structure for her employer.
The problem I am having mainly is how to structure the tables/relationships around the fact that both organisations AND individuals can be members but organisational membership also needs to have 2 named members, with all THEIR contact details too stored. I have tried to create just two tables to store the information to avoid duplicate data entry i.e. one table of organisations (who could be current members, past members, not members at all but have sent paid delegates to an event, current exhibitors, past exhibitors, potential future exhibitors etc) and one table of contacts data (which can be individual members, the contact details of the named members within the organisation, the Admin and Finance contact data for an organisational member, contact details of people who could be non-members but attend as delegates at the events, named individual exhibitors, who can also be linked to one or more organisations if they represent them at an event), then some form of linking tables to show which type they are. But this approach has just become hopelessly confusing, and now I can't see how to create a working table structure that covers all permutations, and have the data entry straightforward in all the different forms.
Some of the other bits I think I can do without help, such as the invoicing/payments/event booking etc, but I have provided all the information given to me below in case anything impacts on the bit I'm struggling with. My challenge is with the tables and relationships around the organisations/membership/contacts part, so would love some pointers please on ways to make this all work together! I've tried looking at loads of DB table designs online but none of them have the permutations that this one seems to have exactly so I can adapt the design and make it work for this case.
As I said, I'm only slightly more experienced than beginner level, so I could be missing something incredibly obvious in how to make this work! So please, be kind
Many thanks for any assistance anyone can offer
The spec I have been sent that she has to work to is below, along with a few questions I have sent her (and her answers) to clarify how certain data is to be used/stored:
Member Organisations – Needs to include the contact details not just of the organisation but admin and finance offices/names/emails etc too
• Each member organisation may have several individual named members each with their own different contact details – including different addresses sometimes.
• Some members are individuals, not just organisation members. There are different types of members – Institutional Members; Ordinary Members; Retired Members; Honorary Members.
• Each member organisation may need to be invoiced several times per year – for annual membership, annual conference & autumn CPD events (occasionally organisations have separate invoicing for accommodation and the event)
o Events invoices may be for individual members, not necessarily all of them.
• Some membership renewals and events bookings come through the website so manual invoicing is not required (though if we had a reliable method for issuing renewals/events invoices easily this may enable us to review the website requirements as few colleges currently use the online ordering process successfully)
• Need to be able to record when payments are made, for what and by what method (card/cheque/BACS) etc
• Need to be able to record which named members within organisations have booked to attend events and which of those require hotel rooms booking/included.
Also, we need to be able to record exhibitors for events, and invoice them too – they are not necessarily members
• Organisations can be members, as can individuals who may be Ordinary, Retired or Honorary Members.
• Institutional Members then have “named members” within that – these are the contacts for members and listed as Members
• For Institutional Members I also have to have both Admin and Finance contacts
• Members are grouped into Regions – this is because they have regional meetings/events – I need to be able to pull off regional contact lists of both current and past/potential members from the DB when requested by the Regional Chairs.
Events: - There are generally, currently, 2 per year.
• Members can attend as delegates, some members may also attend as exhibitors – and there may be more than one contact name on the exhibitor list.
• There may be multiple delegates attending from one organisation so somehow this needs to be shown.
• We can also have non-members attending as delegates
• We also sometimes have other organisations attending as exhibitors – we need to be building up a record of these so we can let them know of future events they may wish to attend.
So, for most members I need the ability to be able to invoice them up to 3 times per year, I need to be able to see easily who has and has not paid and update this as I get information through about who has paid and when – at the moment this information is not in the best layout (certainly not on the spreadsheets!). I need to be able to see easily who needs member badges sent, and their web-user status updating etc as well.
One thing I don’t currently have in the spreadsheets for each member organisation but does need adding in is what years an organisation may have been a member in the past
I need to be able to email either the entire “Member” list of contacts at times, or groups of them (like for instance if something is pertinent to one region only).
I need as I said to be able to build up a separate information base of organisations who either have exhibited or may do so in the future which is obviously not the same as the members list but would be related if they book to attend conferences/CPD events.
In addition to this as a member-led organisation some of the members are also “Regional Representatives” and as such are part of the Executive Committee and this would then also be a group I may need to be able to email at times too.
I need to be able to see who has booked for the conferences/other events through the year and show the invoices that are related to these. I need to be able to create lists of regional members, and need to be able to email blocks of contacts from the DB as well
Delegates can be charged different prices depending whether they are members or non-members – for example the last conference was £450 for members / £600 for non-members. We also did an early-bird booking discount which took the price back to £405. Sometimes a delegate – or exhibitor can only go for a day so we’ll have a different price for them too
Membership prices at present are:
• Institutional - £295 (includes 2 named members within an organisation)
• Ordinary - £75 – this may be for individuals, or additional named members within an organisation where they want more staff to benefit from membership
• Retired - £12 per year
• Honorary - FREE
Membership runs from 1st Jan – 31st Dec each year.
If a member joins mid-year, would they get any kind of discount if the membership expires on, for example, 31st Dec of that year, as they would only be getting 50% of the year?
Yes, this may happen – although often organisations take their time renewing but still continue to attend their regional meetings and will be billed for the entire year as a result
Can these ever be part paid?
They are not normally part-paid, I haven’t had this happen yet.
Do these ever need to be reissued, and data about the reissuing stored, such as a new sent date?
I’ve not had to re-issue invoices – yet – if there’s ever a late payment then I’ll re-send the original invoice, and I keep all emails so always have a track of this, though it might be helpful to be able to put in a chase-up date if that’s possible.
Are there payment deadlines, and do these need to be implemented to strict criteria (i.e you need to receive payments within X days of the invoice being issued or the member benefits won’t be received)?
They are supposed to be settled within a time-frame but because things are so unwieldy at the moment this hasn’t yet been enforced, I just email and nudge with reminders as and when required.
Occasionally a discount may well be applied – as mentioned for conferences etc. Not so much for membership payments though.
How are Purchase Orders related to Invoices? Do invoices only get generated when POs are received? What data need to be stored for POs, i.e. date received etc?
Purchase Orders are received from the organisations– I don’t issue invoices until I’ve received at least a PO number (don’t always get a copy of the PO until later) – the PO’s are always stored in a dropbox folder and I don’t generally retain a date for the PO – I use my rather chaotic emails and search on a college to find that information as required.