Very basic database theory

I have a set of tables to show the relationship between organziations and supporters below. Although I have done some basic mySQL queries, I know very little about database 'design'.

I plan to query the database for:

• a list of contributors to a specific organization... or,
• a list of organizations that a specific supporter supports.

The database tables for organiations and contributors may have other columns in the future and receive a lesser amount of queries based on that 开发者_如何转开发information.

A | X  
A | Y  
A | Z  
B | X  
B | Y  
C | X  
C | Z

How should the tables be set up? I assume that there should be a third table, but there is still redundant information in the third table. Is there a better way of setting up the tables?

+----+-------+    +-------------+----------+   +----+-------+  
| id | org   |    | org         | contr    |   | id | contr.|  
+----+-------+    +-------------+----------+   +----+-------+  
|  1 |   A   |    |           1 |        1 |   |  1 | X     |  
|  2 |   B   |    |           1 |        2 |   |  2 | Y     |  
|  3 |   C   |    |           1 |        3 |   |  3 | Z     |  
+----+-------+    |           2 |        1 |   +----+-------+  
                  |           2 |        2 | 
                  |           3 |        1 |  
                  |           3 |        3 |  
                  +-------------+----------+  

You're solution is perfectly acceptable and I can't see where there is any redundant information. This is the normal way to represent a many to many relationship. Your third table is called a junction table in relational databases.

This is called a many to many relationship, or how you usually encounter it: M:N. There is no way around the third table, and while it contains redundant information, it follows the design rules of relational databases.

Yes, there are tradeoffs, however that's what you get when you push a technology out of its comfortzone. If you look to keep the redundancy to a minimum, a NoSql solution might be worth looking into, however, this might force you lose a lot of the advantages of an RDBMS. You have to weight your alternatives and go with the storage facility suiting you the most.

3 starting tables: contributors, supporters, organizations.

-> An organization has many contributors means that if a contributor has one organization, you just set an organization_id to the contributors table. If it can have many organizations, you need a third table, say organization_contributors that has (organization_id, contributor_id).

-> An organization has many supporters is exactly the same as above :)

You have a typical MN relationship. That is a many-to-many relationship between organizations and contributors.

Since a database engine does not usually support this kind of relationship, you'll need a third table. This join table will reduce this one relationship into two 1M relationship (one to many).

Contributors 1:M ContributorOrganization M:1 Organization

Set up 3 tables like you proposed, e.g.:

CREATE TABLE supporters ( 
   supid INTEGER PRIMARY KEY,  
   name VARCHAR(32)  
);

CREATE TABLE organizations ( 
  orgid INTEGER PRIMARY_KEY,   
  name VARCHAR(32) 
);

CREATE TABLE supports ( 
   supid INTEGER NOT NULL,    
   orgid INTEGER NOT NULL,    
   FOREIGN KEY(supid) REFERENCES supporters(supid),    
   FOREIGN KEY(orgid) REFERENCES organzations(orgid) 
);

You can then create supporters and organizations and add ids of both into the supports table so. (Note: The SQL will work in sqlite, but it's just for demonstration purposes). For each supporter that belongs to an organization, add his supporter id and the organization id in the supports table.

There is no redundancy here; don't worry about duplicating the id's; they're needed to establish the relationship between the supporters and the organizations.

It will look less "redundant" the more columns you add to the supporters and organizations tables.