Re: I can't normalize these tables
Posted by: Rick James
Date: September 02, 2011 09:48AM

Plan A:
One table with nullable fields for type-B info. (The PRIMARY KEY could be an AUTO_INCREMENT.)

Plan B:
Main table contains the common info, such as company name. (The PRIMARY KEY could be an AUTO_INCREMENT.)
A second table contains the extra type-B info, and uses the same PK as the main table, but without AUTO_INCREMENT. With "FROM main LEFT JOIN secondary", you can easily and efficiently stitch the fields back together in a SELECT.

Plan C (an extension of Plan B):
N+1 tables -- one main table, then a secondary table for each 'type'. Stitching back together is messy -- either you have to know the type, or you have to do lots of LEFT JOINs and end up with lots of columns. Or it could be a UNION.

Each Plan has benefits and drawbacks.

Normalization is a nice goal, but there are many cases where you have to bend the rules.

Options: ReplyQuote

Written By
Re: I can't normalize these tables
September 02, 2011 09:48AM

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.