MySQL Forums
Forum List  »  Newbie

Lookup Table with Optional Fields? Null primary key for blanks?
Posted by: Daniel Wingard
Date: September 11, 2012 04:01PM

Forgive my lack of formal terminology - I'm a self-taught MySQL newbie. I'm developing a CRM design and I want to model the following situation:

1. "Data Type A" (necessarily has its own tbl/pk) can be related to other "Data Type A" with "Relationship_ID". Each component references a foreign key in a lookup table "Data Type A Relationships".
2. "Data Type B" has the same setup, and another lookup table "Data Type B Relationships".
3. A third lookup table identifies "Relationship_ID" between a "Data Type A" and "Data Type B" in a third lookup table.
This requires 3 data tables: "Data Type A", "Data Type B", and "Relationships" and 3 lookup tables.

I'm curious if I can responsibly merge the 3 lookup tables into 1 that looks something like a table with these attributes:

1) PK: Data Type A, ref 1 (FK to Data Type A)
2) PK: Data Type A, ref 2 (FK to Data Type A)
3) PK: Data Type B, ref 1 (FK to Data Type B)
4) PK: Data Type B, ref 2 (FK to Data Type B)
5) PK: Relationship_ID (FK to Relationship_ID)

I am concerned that if I do this, the Unique PK (1,2,3,4,5) must maintain two NULL values and any subsequent UPDATE or SELECT must identify which of the elements are NULL. UPDATEs will have to manage that setup.

Should I be avoiding the added complexity of combining to save 2 lookup tables? Once I get better at managing my dataset will this difference become trivial?

Options: ReplyQuote


Subject
Written By
Posted
Lookup Table with Optional Fields? Null primary key for blanks?
September 11, 2012 04:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.