MySQL Forums :: Database Design & Data Modelling :: Help w/ data modeling, 1 to 1, 1 to none? hack?


Advanced Search

Help w/ data modeling, 1 to 1, 1 to none? hack?
Posted by: Ron Creamer ()
Date: February 06, 2009 04:33PM

Greetings,

I need advice on the optimal way to spec my sql database tables. I'm stuck between a couple of lousy alternatives to a rather simple problem which I'm sure has been solved before.

Let's assume there is a table called 'paper'
Each paper record holds all the characteristics of this type of paper.
Some papers, but not nearly all, have a unique characteristic. In my example, the unique characteristic is that this paper is "FSC Certified".

For those that are FSC Certified, there will be a 2 fields to track. My thinking is to normalize by putting these two fields in another table, let's call it the 'fsc' table. The two fields are simply a varchar field holding the FSC Certificate Number, and the other field will be a blob/scan/PDF of the actual certificate.

So in my 'paper' table, I'm thinking that I should have a fk (foreign key) to the 'fsc' table's pk (primary key).

But this doesn't work because only about 15% of my 'paper' table records are FSC Certified. And the other 85% would have a null foreign key.

Any advice on how to better model this?

Thanks in advance for your help.

Options: ReplyQuote


Subject Written By Posted
Help w/ data modeling, 1 to 1, 1 to none? hack? Ron Creamer 02/06/2009 04:33PM
Re: Help w/ data modeling, 1 to 1, 1 to none? hack? Rick James 02/07/2009 05:06PM
Re: Help w/ data modeling, 1 to 1, 1 to none? hack? Ron Creamer 02/10/2009 03:33PM
Re: Help w/ data modeling, 1 to 1, 1 to none? hack? Rick James 02/14/2009 12:37AM


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.