MySQL Forums :: Newbie :: Lookup Table with Optional Fields? Null primary key for blanks?


Advanced Search

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? Daniel Wingard 09/11/2012 04:01PM
Re: Lookup Table with Optional Fields? Null primary key for blanks? Rick James 09/13/2012 09:13AM
Re: Lookup Table with Optional Fields? Null primary key for blanks? Daniel Wingard 09/13/2012 02:15PM
Re: Lookup Table with Optional Fields? Null primary key for blanks? Rick James 09/15/2012 01:35PM
Re: Lookup Table with Optional Fields? Null primary key for blanks? Daniel Wingard 09/15/2012 04:56PM


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.