CODEWITHSUNDEEP
database-designfiltering
Bakaburg
Bakaburg

Reputation: 3311

Best database building practice for members list

I want to make a members database for my association. We are a national organization that is composed by smaller local entities.

I was wondering which practice would be better to organize my database in the mean of speed and overhead:

  1. Create one large table with all the members with a "local association" field for filtering.
  2. Create many tables, one for each group.

(btw I'm working on joomla, if this could change something)

EDIT: I'll make clearer the structure of the association. One member can be part of only one small local association at time. All member are part of the national association. For every member (and so every associations) the data fields are the same. Comment me if it's still not clear :)

Upvotes: 0

Views: 158

Answers (4)

swisscheese
swisscheese

Reputation: 1775

With the limited requirements that you have posted this is how I see it. It is a third alternative because the 2 options you posted really are not appropriate for a DB.

enter image description here

You describe 3 different entities (Member, Group (Assoc.), Address (Location)) and per rules of normalization you need to separate those appropriately. If you keep everything in only 1 or 2 tables you will have duplicates throughout your DB. Personally I agree with Thomas here and would look into incorporating the second option that I posted.

I just gave a basic table structure, again not understanding your full requirements, so please adjust the Relationship Cardinality as you see fit for your requirements. The most important part of my answer that I am trying to stress is normalize appropriately. Contrary to what others say about simplicity you can't get any more simple then having a properly set up database that in turn is properly normalized.

Upvotes: 2

Thomas
Thomas

Reputation: 64645

I would consider a third alternative: a table of members, a table of associations and a table that associates the two together. In this way, the same person can be part of multiple associations without having to duplicate their information.

Upvotes: 1

Mark Nenadov
Mark Nenadov

Reputation: 6927

I would suggest one large table. Even if splitting it up were theoretically faster, you'd lose a lot of advantages in terms of simplicity. For instance, in relation to speed, consider the simplicity of tuning and optimizing queries against one table as opposed to 10 tables.

Also I'm concerned that if you have multiple tables, you would be having a lot of redundancy. So by all means, if you do split, try to minimize redundancy and use relationships to avoid having to maintain tons of almost identical tables.

Upvotes: 0

John Bledsoe
John Bledsoe

Reputation: 17652

Assuming that you are tracking the same fields for all members, regardless of local association, I would lean towards one table for all members. This will significantly lower your maintenance overhead when you add/remove local associations.

If different local associations track significantly different information about members, then you may need to reconsider this recommendation a bit.

Upvotes: 1

Related Questions