How to setup 1:many relationships in MySQL

Question

Hello folks,

I'm trying to build a searchable database of acronyms and their definitions that are specific to a certain industry. It has been years since I've done any real programming, so I'm a little behind the learning curve.

I'm writing the code in PHP, and I'm using MySQL as the database. If this can be done easier in Postgres, I'm not opposed to switching DBs, but I can't use Oracle or any other commercial system.

So here's the question:

I'd like to set it up so that each acronym can: (1) apply to 1, multiple, or no specific organizations; (2) have 1 or more associated definitions.

The complexity--at least in my mind :D--comes in that it is conceivable that some organizations might have a single acronym with multiple definitions that all relate to that one organization. At the same time, the acronym may have 1 or more definitions that relate to OTHER organizations as well.

Am I over complicating this?

I'd like to better understand how to setup the table structure and relationships in MySQL--what fields and relationships would be in each table.

A SQL statement would be helpful if anyone feels so inclined, but I'm hoping to at least get a solid grasp on the database schema so I can get the tables created and some sample data imported.

Many, many thanks to all...

Dan

Answer 1

If I understand your question, you can use three separate tables. First, have the table of acronyms/definitions, then have a table of Organizations. Finally, have an AcronymOrganization table, that just references a key from the acronym table, and a key from the organization table. This way, you can have as many acronyms for an organization as you please.

After you set up the database, you need to use a couple inner joins to join the three tables, collecting only the acronyms for the appropriate organization ID.

Answer 2

I'd just create an acronym table, an organization table, and a definition table. Put two foreign keys in the definition table: one for the entry in the acronym table, and the other for the entry in the organization table.

Answer 3

If you want to have a n:m relationship between tableA and tableB, then you need a third table.

table A. Fields : ID,name

table B. Fields : ID,name

table AB. Fields : A,B (A is a reference to A.ID, B is a reference to B.ID)

[TABLEA]1-----*[TABLE_AB]*-----1[TABLEB]

Example

Contents of table a:

ID Name
1  John
2  Mary
3  Piet

Contents of Table b:

ID Name
1  Microsoft
2  Google
3  Philips

Contents of Table ab:

ID Name
1  2
1  3
2  2
3  1
3  3

Then select everything like this:

select a.name,b.name 
from a,b,ab 
where a.id=ab.a and b.id=ab.b

Result:

a.name  b.name
John    Google
John    Philips
Mary    Google
Piet    Microsoft     
Piet    Philips

Answer 4

The solution should contain 4 tables: Acronyms, Definitions, Organizations, and AcronymOrganization.

Acronym(id, acronym, definition_id)
Definitions(id, definition)
Organizations(id, organization)
AcronymOrganization(id, acronym_id, organization_id)