SEO

'morning everyone!

I hope not to violate any rule by posing this question... If so, I'm sorry
and please forgive me! I searched Internet and books for a solution with no
success, this is why I'm asking you an hint...

Let me briefly expose my problem: I'm a software engineer student and I'm
working on a demonstrative project to discuss it during my exam...

I focused on glossary, one of the documents produced during the developing
process of a database. It's the transposition in tabular form of concepts
expressed in natural language and gathered during the analysis phase of the
project. The glossary table contains entries (names of concepts),
description (description of concepts), synonims' list (can be blank), list
of entries linked to the current one (can't be blank). I decided to keep
things simple so I simply added the concept of project's names to permit the
handling of multiple glossary.

I passed by each step of the developing process quite easily, but when i
finally checked out the correctness of my work I discovered that something
was wrong... I tryed to fix it but with no success... I'm going to report
the SQL statements I wrote and the resulting table affected by the problem:




First of all, I created the data structures:
============================================

CREATE SCHEMA archive;

USE archive;

CREATE TABLE projects (
name VARCHAR(50) NOT NULL,
PRIMARY KEY (name));

CREATE TABLE glossary (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
decription TEXT NOT NULL,
PRIMARY KEY (name, entry),
FOREIGN KEY (name) REFERENCES projects (name));

CREATE TABLE synonims (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
option VARCHAR(50) NOT NULL,
PRIMARY KEY (name, entry, option),
FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));

CREATE TABLE linking (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
target VARCHAR(50) NOT NULL,
PRIMARY KEY (name, entry, target),
FOREIGN KEY (name, entry) REFERENCES glossary (name, entry),
FOREIGN KEY (name, target) REFERENCES glossary (name, entry));



Afterwards I populated the tables with a few sample data:
================================================== =======

INSERT INTO projects VALUES (
'example');

INSERT INTO glossary VALUES (
'example', 'person', 'example of a typical entity');

INSERT INTO glossary VALUES (
'example', 'name', 'example of a typical attribute');

INSERT INTO synonims VALUES (
'example', 'name', 'surname');

INSERT INTO synonims VALUES (
'example', 'name', 'nickname');

INSERT INTO linking VALUES (
'example', 'person', 'name');



Finally I developed the SQL statement to get the desired table:
================================================== =============

SELECT g.entry, g.description,
GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
GROUP_CONCAT(c.target SEPARATOR ', ') AS links
FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
ON g.name = l.name AND g.entry =
WHERE g.name = 'example'
GROUP BY l.name, l.entry
UNION
SELECT g.entry, g.description,
GROUP_CONCAT(s.option SEPARATOR ', '),
GROUP_CONCAT(c.target SEPARATOR ', ') AS links
FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
WHERE g.name = 'example'
GROUP BY l.name, l.target;



And this is what the above SQL statement produces:
==================================================

+--------+------------------------------+-------------------+----------------+
| entry | description | options | links
|
+--------+------------------------------+-------------------+----------------+
| person | example of a typical entity | NULL | name
|
| name | example of typical attribute | surname, nickname | person, person
| (*)
+--------+------------------------------+-------------------+----------------+

As you can see, the 'links' field of the record whose entry in 'name'
contains a double reference to 'person' (*) while I expected a single
reference...

Does anyone can help? Thank you all in advance, please let me know,

Stefano Bragaglia

Stefano Bragaglia wrote:
> 'morning everyone!
>
> I hope not to violate any rule by posing this question... If so, I'm sorry
> and please forgive me! I searched Internet and books for a solution with no
> success, this is why I'm asking you an hint...
>
> Let me briefly expose my problem: I'm a software engineer student and I'm
> working on a demonstrative project to discuss it during my exam...
>
> I focused on glossary, one of the documents produced during the developing
> process of a database. It's the transposition in tabular form of concepts
> expressed in natural language and gathered during the analysis phase of the
> project. The glossary table contains entries (names of concepts),
> description (description of concepts), synonims' list (can be blank), list
> of entries linked to the current one (can't be blank). I decided to keep
> things simple so I simply added the concept of project's names to permit the
> handling of multiple glossary.
>
> I passed by each step of the developing process quite easily, but when i
> finally checked out the correctness of my work I discovered that something
> was wrong... I tryed to fix it but with no success... I'm going to report
> the SQL statements I wrote and the resulting table affected by the problem:
>
>
>
>
> First of all, I created the data structures:
> ============================================
>
> CREATE SCHEMA archive;
>
> USE archive;
>
> CREATE TABLE projects (
> name VARCHAR(50) NOT NULL,
> PRIMARY KEY (name));
>
> CREATE TABLE glossary (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> decription TEXT NOT NULL,
> PRIMARY KEY (name, entry),
> FOREIGN KEY (name) REFERENCES projects (name));
>
> CREATE TABLE synonims (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> option VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, option),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));
>
> CREATE TABLE linking (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> target VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, target),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry),
> FOREIGN KEY (name, target) REFERENCES glossary (name, entry));
>
>
>
> Afterwards I populated the tables with a few sample data:
> ================================================== =======
>
> INSERT INTO projects VALUES (
> 'example');
>
> INSERT INTO glossary VALUES (
> 'example', 'person', 'example of a typical entity');
>
> INSERT INTO glossary VALUES (
> 'example', 'name', 'example of a typical attribute');
>
> INSERT INTO synonims VALUES (
> 'example', 'name', 'surname');
>
> INSERT INTO synonims VALUES (
> 'example', 'name', 'nickname');
>
> INSERT INTO linking VALUES (
> 'example', 'person', 'name');
>
>
>
> Finally I developed the SQL statement to get the desired table:
> ================================================== =============
>
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
> GROUP_CONCAT(c.target SEPARATOR ', ') AS links
> FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
> ON g.name = l.name AND g.entry =
> WHERE g.name = 'example'
> GROUP BY l.name, l.entry
> UNION
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', '),
> GROUP_CONCAT(c.target SEPARATOR ', ') AS links
> FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
> WHERE g.name = 'example'
> GROUP BY l.name, l.target;
>
>
>
> And this is what the above SQL statement produces:
> ==================================================
>
> +--------+------------------------------+-------------------+----------------+
> | entry | description | options | links
> |
> +--------+------------------------------+-------------------+----------------+
> | person | example of a typical entity | NULL | name
> |
> | name | example of typical attribute | surname, nickname | person, person
> | (*)
> +--------+------------------------------+-------------------+----------------+
>
> As you can see, the 'links' field of the record whose entry in 'name'
> contains a double reference to 'person' (*) while I expected a single
> reference...
>
> Does anyone can help? Thank you all in advance, please let me know,
>
> Stefano Bragaglia
>
>

As it should. You have both surname and nickname in your options, and
each is a link to a person. And you show two links.

There is nothing in your setup which indicates surname and nickname must
belong to the same person.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Good morning Jerry and thank you for your reply!

I'm obviously doing something wrong, but I cannot find out where the error
is!

Can you please suggest me where to look for it? Or where my reasoning is
leaking something?

I can provide the description of the problem in natural language, the E/R
diagram and the relational diagram if it can help...

Thank you in advance,
Stefano Bragaglia

Hello again...

I just put together this SQL statement that produces the expected output...

SELECT g.entry, g.description,
GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
GROUP_CONCAT(DISTINCT l.target SEPARATOR ', ') AS linking
FROM glossary g NATURAL LEFT JOIN synonims s NATURAL JOIN linking l
GROUP BY g.name, g.entry
UNION
SELECT g.entry, g.description,
GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
GROUP_CONCAT(DISTINCT l.entry SEPARATOR ', ') AS linking
FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
ON l.name = g.name AND l.target = g.entry
WHERE g.name = 'example'
GROUP BY g.name, g.target
ORDER BY entry;

Can I safely retain the problem solved or do I still miss something?

Thank you again,
Stefano

Stefano Bragaglia wrote:
> Good morning Jerry and thank you for your reply!
>
> I'm obviously doing something wrong, but I cannot find out where the error
> is!
>
> Can you please suggest me where to look for it? Or where my reasoning is
> leaking something?
>
> I can provide the description of the problem in natural language, the E/R
> diagram and the relational diagram if it can help...
>
> Thank you in advance,
> Stefano Bragaglia
>
>

You have a basic problem in your logic.

You are saying the "name" has the option "surname", which is linked to
"person". That's OK.

You're also saying that "name" has the option "nickname", which is
linked to "person". That in itself is OK.

But your problem is that, the way you have it, "surname" and "nickname"
could be from two different persons. And I don't think you want the
surname from "John Doe" and the nickname from "Chi Chi Cordova".

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hello Jerry!

Thank you again for replaying!

> You are saying the "name" has the option "surname", which is linked to
> "person". That's OK.
>
> You're also saying that "name" has the option "nickname", which is linked
> to "person". That in itself is OK.
>
> But your problem is that, the way you have it, "surname" and "nickname"
> could be from two different persons. And I don't think you want the
> surname from "John Doe" and the nickname from "Chi Chi Cordova".

If this is what comes out from my work, then it's simply wrong!
What I wanted to express is the following (sorry if it will take a little
long to explain things in details):

Everytime I develop a project, I have to compile a glossary. Each
project is unambiguously determined by its name.
Each glossary is a list of entries. Each entry is an idea discussed into
the project and is composed by:
- a name: a string that univocally stands for the entire entry (note:
different glossaries can have entries with the same name);
- a description: a long text detailing everything about the entry;
- a list of synonims: the strings in the list are other names with whom
the entry can be known (note: the list can be blank);
- a list of terms: these terms are other terms of the same project with
whom current term is binded (note: this list can't be blank because every
entry must be
part of the project; in other word if a term is not binded with the
rest of its project then probably it's not part of the project).

Let's express, for example, the base E/R Diagrams concepts with the above
system:
The project's name can be "E/R Diagrams" and the entries can be "entity",
"relationship", "attribute" and "class".
Entry "entity" can be expressed by: "entity", "a discrete object, etc.",
"table", "attribute, class, relationship".
On the other hand, entry "relationship" can be expressed by: "relationship",
"express how two entities are associated, etc.", NULL, "attribute, entity".
Again,for entry "attribute" we have: "attribute", "typical trait of
relationships or entities, etc.", "field, column", "attribute,
relationship".
And finally, for entry "class": "class", "parenthood between entities,
etc.", NULL, "class".

Then I translated the above description into the following diagram:

has
+-----------+ + +-----------+
Name *---| | (1,1) / \ (1,1) | |
| Projects +-------+ +-------+ Glossary |
| | \ / | |
+-----------+ + +-----+-----+
(0,n) |
+
/ \
contains + +
\ /
+
(1,1) |
+-----+-----+
| |---* Name
| Entry |---o Description
| |---o Synonims (0,n)
+-+-------+-+
| + |
(1,n) | / \ | (1,n)
+-+ +-+
\ /
+
linked

After a few refining step, I started to translate it into logical schema:

+-----------+
Name *---| |
| Projects |
| |
+-----------+


*
+-----------+ |
Description o---| |-+-o Name (Projects.Name)
| Glossary |-+-o Entry
| | |
+-----------+


*
+-----------+ |
| |-+-o Name (Glossary.Name)
| Synonims |-+-o Entry (Glossary.Entry)
| |-+-o Option
+-----------+ |


*
+-----------+ |
| |-+-o Name (Glossary.Name)
| Linking |-+-o Entry (Glossary.Entry)
| |-+-o Target (Glossary.Target)
+-----------+ |

The last diagram is best expressed at logical level by the following:
Projects (NAME)
Glossary ([NAME], ENTRY, description)
Synonims ([NAME], [ENTRY], OPTION)
Linking ([NAME], [ENTRY], [TARGET-->ENTRY])

These table are 1NF, 2NF, 3NF and BCNF, so can be safely translated into
physical level by the following SQL:

CREATE SCHEMA glossaries;
USE glossaries;
CREATE TABLE projects (
name VARCHAR(50) NOT NULL,
PRIMARY KEY (name));
CREATE TABLE glossary (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
decription TEXT NOT NULL,
PRIMARY KEY (name, entry),
FOREIGN KEY (name) REFERENCES projects (name));
CREATE TABLE synonims (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
option VARCHAR(50) NOT NULL,
PRIMARY KEY (name, entry, option),
FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));
CREATE TABLE linking (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
target VARCHAR(50) NOT NULL,
PRIMARY KEY (name, entry, target),
FOREIGN KEY (name, entry) REFERENCES glossary (name, entry),
FOREIGN KEY (name, target) REFERENCES glossary (name, entry));

If this is not correct, where lies the error?
I tryed to carefully follow the suggestions of my teacher... I presumed that
I developed the whole process correctly...
I probably misunderstood somenthing, but what? and where?

In the meanwhile, I also produced the following SQL query that seems to work
correctly (as I already posted):

SELECT g.entry, g.description,
GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
GROUP_CONCAT(DISTINCT l.target SEPARATOR ', ') AS linking
FROM glossary g NATURAL LEFT JOIN synonims s NATURAL JOIN linking l
GROUP BY g.name, g.entry
UNION
SELECT g.entry, g.description,
GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
GROUP_CONCAT(DISTINCT l.entry SEPARATOR ', ') AS linking
FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
ON l.name = g.name AND l.target = g.entry
WHERE g.name = 'example'
GROUP BY g.name, g.target
ORDER BY entry;

Now I'm asking myself: this work is correct or I'm obtaining the expected
output by pure chance?

Thank you in advance for reading and sorry again for the long post!

Stefano Bragaglia

Stefano Bragaglia wrote:
> Hello Jerry!
>
> Thank you again for replaying!
>
>
>>You are saying the "name" has the option "surname", which is linked to
>>"person". That's OK.
>>
>>You're also saying that "name" has the option "nickname", which is linked
>>to "person". That in itself is OK.
>>
>>But your problem is that, the way you have it, "surname" and "nickname"
>>could be from two different persons. And I don't think you want the
>>surname from "John Doe" and the nickname from "Chi Chi Cordova".
>
>
> If this is what comes out from my work, then it's simply wrong!
> What I wanted to express is the following (sorry if it will take a little
> long to explain things in details):
>
> Everytime I develop a project, I have to compile a glossary. Each
> project is unambiguously determined by its name.
> Each glossary is a list of entries. Each entry is an idea discussed into
> the project and is composed by:
> - a name: a string that univocally stands for the entire entry (note:
> different glossaries can have entries with the same name);
> - a description: a long text detailing everything about the entry;
> - a list of synonims: the strings in the list are other names with whom
> the entry can be known (note: the list can be blank);
> - a list of terms: these terms are other terms of the same project with
> whom current term is binded (note: this list can't be blank because every
> entry must be
> part of the project; in other word if a term is not binded with the
> rest of its project then probably it's not part of the project).
>
> Let's express, for example, the base E/R Diagrams concepts with the above
> system:
> The project's name can be "E/R Diagrams" and the entries can be "entity",
> "relationship", "attribute" and "class".
> Entry "entity" can be expressed by: "entity", "a discrete object, etc.",
> "table", "attribute, class, relationship".
> On the other hand, entry "relationship" can be expressed by: "relationship",
> "express how two entities are associated, etc.", NULL, "attribute, entity".
> Again,for entry "attribute" we have: "attribute", "typical trait of
> relationships or entities, etc.", "field, column", "attribute,
> relationship".
> And finally, for entry "class": "class", "parenthood between entities,
> etc.", NULL, "class".
>
> Then I translated the above description into the following diagram:
>
> has
> +-----------+ + +-----------+
> Name *---| | (1,1) / \ (1,1) | |
> | Projects +-------+ +-------+ Glossary |
> | | \ / | |
> +-----------+ + +-----+-----+
> (0,n) |
> +
> / \
> contains + +
> \ /
> +
> (1,1) |
> +-----+-----+
> | |---* Name
> | Entry |---o Description
> | |---o Synonims (0,n)
> +-+-------+-+
> | + |
> (1,n) | / \ | (1,n)
> +-+ +-+
> \ /
> +
> linked
>
> After a few refining step, I started to translate it into logical schema:
>
> +-----------+
> Name *---| |
> | Projects |
> | |
> +-----------+
>
>
> *
> +-----------+ |
> Description o---| |-+-o Name (Projects.Name)
> | Glossary |-+-o Entry
> | | |
> +-----------+
>
>
> *
> +-----------+ |
> | |-+-o Name (Glossary.Name)
> | Synonims |-+-o Entry (Glossary.Entry)
> | |-+-o Option
> +-----------+ |
>
>
> *
> +-----------+ |
> | |-+-o Name (Glossary.Name)
> | Linking |-+-o Entry (Glossary.Entry)
> | |-+-o Target (Glossary.Target)
> +-----------+ |
>
> The last diagram is best expressed at logical level by the following:
> Projects (NAME)
> Glossary ([NAME], ENTRY, description)
> Synonims ([NAME], [ENTRY], OPTION)
> Linking ([NAME], [ENTRY], [TARGET-->ENTRY])
>
> These table are 1NF, 2NF, 3NF and BCNF, so can be safely translated into
> physical level by the following SQL:
>
> CREATE SCHEMA glossaries;
> USE glossaries;
> CREATE TABLE projects (
> name VARCHAR(50) NOT NULL,
> PRIMARY KEY (name));
> CREATE TABLE glossary (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> decription TEXT NOT NULL,
> PRIMARY KEY (name, entry),
> FOREIGN KEY (name) REFERENCES projects (name));
> CREATE TABLE synonims (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> option VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, option),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));
> CREATE TABLE linking (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> target VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, target),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry),
> FOREIGN KEY (name, target) REFERENCES glossary (name, entry));
>
> If this is not correct, where lies the error?
> I tryed to carefully follow the suggestions of my teacher... I presumed that
> I developed the whole process correctly...
> I probably misunderstood somenthing, but what? and where?
>
> In the meanwhile, I also produced the following SQL query that seems to work
> correctly (as I already posted):
>
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
> GROUP_CONCAT(DISTINCT l.target SEPARATOR ', ') AS linking
> FROM glossary g NATURAL LEFT JOIN synonims s NATURAL JOIN linking l
> GROUP BY g.name, g.entry
> UNION
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
> GROUP_CONCAT(DISTINCT l.entry SEPARATOR ', ') AS linking
> FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
> ON l.name = g.name AND l.target = g.entry
> WHERE g.name = 'example'
> GROUP BY g.name, g.target
> ORDER BY entry;
>
> Now I'm asking myself: this work is correct or I'm obtaining the expected
> output by pure chance?
>
> Thank you in advance for reading and sorry again for the long post!
>
> Stefano Bragaglia
>
>


Stefano,

Again, the problem is that you 'name' and 'nickname' could be two
different people. You need to change your logic so that they both
indicate the same person.

The problem is not necessarily with the way the tables are set up. It's
with your data entries.

You do:

INSERT INTO glossary VALUES (
'example', 'name', 'example of a typical attribute');

INSERT INTO synonims VALUES (
'example', 'name', 'surname');

INSERT INTO synonims VALUES (
'example', 'name', 'nickname');

OK, glossary contains a name. And synonims (sic) contains synonyms of
name being surname and nickname.

But no place have you indicated that these two come from the same row.

Actually, this looks very much like an academic exercise, not something
to be used in the real world. Is it?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry,

thank you again for your kindness! I hope not to bother you with my
questions

Yes, what I proposed here is an academic exercise... and I hope it will grow
into a working project to pass the exam...

> OK, glossary contains a name. And synonims (sic) contains synonyms of
> name being surname and nickname.
>
> But no place have you indicated that these two come from the same row.

What do you mean? Referring to the following bit of code, I thought that the
last line is doing the trick... I mean, doesn't the FOREIGN KEY clause
actually bind each synonim with the same correct row in table "glossary"?
If I'm still wrong, can you please show me how to indicate that objects
comes from the same row?

CREATE TABLE synonims (
name VARCHAR(50) NOT NULL,
entry VARCHAR(50) NOT NULL,
option VARCHAR(50) NOT NULL,
PRIMARY KEY (name, entry, option),
FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));

I know I'm looking like the guy who looks at the finger when you point out
something, but I really don't understand where is my error... Are you
suggesting me to use a simple unique table to do the whole job? If so,
yes... it could be easier to manage, but I also have to develop a little
WAMP solution and I'm planning to handle things (synonims, for example) as
list of strings and not as a single string to edit...

Still thank you, forgive my dumbness,
Stefano Bragaglia

Stefano Bragaglia wrote:
> Jerry,
>
> thank you again for your kindness! I hope not to bother you with my
> questions
>
> Yes, what I proposed here is an academic exercise... and I hope it will grow
> into a working project to pass the exam...
>
>
>>OK, glossary contains a name. And synonims (sic) contains synonyms of
>>name being surname and nickname.
>>
>>But no place have you indicated that these two come from the same row.
>
>
> What do you mean? Referring to the following bit of code, I thought that the
> last line is doing the trick... I mean, doesn't the FOREIGN KEY clause
> actually bind each synonim with the same correct row in table "glossary"?
> If I'm still wrong, can you please show me how to indicate that objects
> comes from the same row?
>
> CREATE TABLE synonims (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> option VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, option),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));
>
> I know I'm looking like the guy who looks at the finger when you point out
> something, but I really don't understand where is my error... Are you
> suggesting me to use a simple unique table to do the whole job? If so,
> yes... it could be easier to manage, but I also have to develop a little
> WAMP solution and I'm planning to handle things (synonims, for example) as
> list of strings and not as a single string to edit...
>
> Still thank you, forgive my dumbness,
> Stefano Bragaglia
>
>

In your SELECT statement you are. But there is NOTHING in your design
which does it. That's the problem.

These two rows:

INSERT INTO synonims VALUES (
'example', 'name', 'surname');

INSERT INTO synonims VALUES (
'example', 'name', 'nickname');

Are independent rows. There is nothing to tie 'surname' and 'nickname'
to the same row. They can easily point to two different rows.

It's the difference between database design and the data itself. And
it's why, in your first try, you got two 'person' entries returned - one
for surname and one for nickname. The result were correct - you were
referring to two 'person' entries.

Your problem is that your 'synonims' table 'options' field contains two
different types of data - a 'name' and a 'surname'.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry,

I give up! I understand I did a mistake but I have no clue on how to solve
it...

I just checked my notes and my books but I didn't find anything that
helps... I think I need an example or I will never pass over this little
accident...

Can you please point me out a resource where I can learn how to handle this
thing or can you show me how you would solve this particular problem? I
would really appreciate if you can...

Thank you for your patience,
Stefano