OK folks, please bear in mind I am a casual MySQL user, but not
novice (at least I don't think so, I could be wrong). Anyway, I am
trying to understand the best way to implement this schema.

Let's say the database is going to store fruit. Each fruit can also
contain another fruit.

So, without repeating and if I understand normalization correctly, I
need 1 table to contain fruits and a fruit_id. Another table would
contain fruit_id, and <I am lost at this point>. I understand the
need to not repeat data, but I cannot seem to understand the logic
behind it.

How can I possibly store fruits that may contain other fruits,
without repeating data such as the fruits themselves?

For example:

table: fruits
id: 1
fruit: grapes
fruit_id: g1

id: 2
fruit: apples
fruit_id: a1

table: associated_fruits
id: 1
-- failure of 1NF (or is it?) if I put the fruit name here
?: ?
fid: a1, g1

So SOMETHING along these lines. I think I do not have enough
experience to look at this without blurring the lines. I cannot see
the woods because of the trees, so to speak.

I also may not understand the first normal form. I am thinking 1NF
pertains to one table, not 2 or more. Thereby my 2nd table *could*
contain the fruit. Is this right?

Sorry, if I explained this poorly, I have difficulties when it comes
to logistics.

Any ideas?

Thanks!

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

-Lost wrote:
> OK folks, please bear in mind I am a casual MySQL user, but not
> novice (at least I don't think so, I could be wrong).

Thats something I kind of think about myself too...

> table: fruits
> id: 1
> fruit: grapes
> fruit_id: g1
>
> id: 2
> fruit: applesthe
> fruit_id: a1

It feels a bit like you have two id's for each fruit, you could drop one
of the id's if they aren't used in another association.


> table: associated_fruits
> id: 1
> -- failure of 1NF (or is it?) if I put the fruit name here
> ?: ?
> fid: a1, g1

Just use the id's, you have the rest of the data in the fruits table and
yes it would go against the optimal normalization (4th NF).

Of course you should also take a look at what data you frequently need
to fetch and you do not want to need to join too many tables, in some
cases you can gain a little bit speed by not make the optimal normalization.

--

//Aho

You are right with the first part : Table 1 = Fruits then create a second
table e.g FruitRelationships , this would have at least 2 columns :
Fruit_id1 , Fruit_id2
you could then add as many relationshipts as you require. So if you need to
change the spelling of the fruit , you change it in the Fruits table , if
you
need to change the relationships , you change in FruitRelationships

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com/mySQL




"-Lost" <maventheextrawords@techie.com> wrote in message
news:Xns99B42C7968146lostthreads@216.196.97.136...
> OK folks, please bear in mind I am a casual MySQL user, but not
> novice (at least I don't think so, I could be wrong). Anyway, I am
> trying to understand the best way to implement this schema.
>
> Let's say the database is going to store fruit. Each fruit can also
> contain another fruit.
>
> So, without repeating and if I understand normalization correctly, I
> need 1 table to contain fruits and a fruit_id. Another table would
> contain fruit_id, and <I am lost at this point>. I understand the
> need to not repeat data, but I cannot seem to understand the logic
> behind it.
>
> How can I possibly store fruits that may contain other fruits,
> without repeating data such as the fruits themselves?
>
> For example:
>
> table: fruits
> id: 1
> fruit: grapes
> fruit_id: g1
>
> id: 2
> fruit: apples
> fruit_id: a1
>
> table: associated_fruits
> id: 1
> -- failure of 1NF (or is it?) if I put the fruit name here
> ?: ?
> fid: a1, g1
>
> So SOMETHING along these lines. I think I do not have enough
> experience to look at this without blurring the lines. I cannot see
> the woods because of the trees, so to speak.
>
> I also may not understand the first normal form. I am thinking 1NF
> pertains to one table, not 2 or more. Thereby my 2nd table *could*
> contain the fruit. Is this right?
>
> Sorry, if I explained this poorly, I have difficulties when it comes
> to logistics.
>
> Any ideas?
>
> Thanks!
>
> --
> -Lost
> Remove the extra words to reply by e-mail. Don't e-mail me. I am
> kidding. No I am not.

Response from "J.O. Aho" <user@example.net>:

> -Lost wrote:
>> OK folks, please bear in mind I am a casual MySQL user, but not
>> novice (at least I don't think so, I could be wrong).
>
> Thats something I kind of think about myself too...
>
>> table: fruits
>> id: 1
>> fruit: grapes
>> fruit_id: g1
>>
>> id: 2
>> fruit: applesthe
>> fruit_id: a1
>
> It feels a bit like you have two id's for each fruit, you could
> drop one of the id's if they aren't used in another association.
>
>
>> table: associated_fruits
>> id: 1
>> -- failure of 1NF (or is it?) if I put the fruit name here
>> ?: ?
>> fid: a1, g1
>
> Just use the id's, you have the rest of the data in the fruits
> table and yes it would go against the optimal normalization (4th
> NF).
>
> Of course you should also take a look at what data you frequently
> need to fetch and you do not want to need to join too many tables,
> in some cases you can gain a little bit speed by not make the
> optimal normalization.

Ack! I totally forgot to respond to this thread. I read it though!
For some reason I did not think to respond until I just saw Mr.
Vamvas' response. Sheesh, sorry J.O. Aho.

Thanks for your feedback, it ed me to understand it a little
better.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

Response from "Jack Vamvas" <DEL_TO_REPLY@del.com>:

> You are right with the first part : Table 1 = Fruits then create a
> second table e.g FruitRelationships , this would have at least 2
> columns : Fruit_id1 , Fruit_id2
> you could then add as many relationshipts as you require. So if
> you need to change the spelling of the fruit , you change it in
> the Fruits table , if you
> need to change the relationships , you change in
> FruitRelationships

That makes a lot of sense too, thanks Mr. Vamvas.

Just to be sure I totally undersand, Fruit_id1 would probably be the
actual Fruit_id1 from the Fruits table, where Fruit_id2 would be the
primary auto incremented key in the FruitRelationships table, as in:

table: Fruits
Fruit_id1: 1
Fruit_name: grapes

table: FruitRelationships
Fruit_id1: 1
Fruit_id2: 1

....is that what you meant? I hope so because it makes sense to me.
Hehe.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.