Multiple Ownerships

Report · Jun 16, 2007

Let's say I have to connect single/multiple people to a single/multiple logo (representing workmanship qualifications)

They are 'BAFTA' 'NVQ' 'GCSE' 'OLEVEL' 'DEGREE' etc - these will appear as images at the foot of the person's personal page...

DAVID has 'BAFTA' 'NVQ'
MARY has 'NVQ' 'GSCE' 'OLEVEL'
COLIN has 'DEGREE' 'NVQ'

This is what I have in my head, to show you my problem
So I create two tables PEOPLE and LOGOS and when inputting the data into MYSQLis where I hit a brick wall! Attach the people to the logo or the logo to the people? I think its better to input the peron's details and then have a dropdown menu of all the logos available and then input that. Or maybe a series of dynamic checkboxes that I can tick to indicate which logos the person has. But how does this work? Are they all stored in a single field on the database "person.logos" or do they need to have a single field for each logo "person.logos_01[BAFTA]" "person.logos_01[NVQ]" "person.logos_01[NULL]" etc etc ? And what would be the actual MYSQL code?

INSERT INTO person.logos ( 'BAFTA', 'NVQ' ,'GCSE', 'OLEVEL', 'DEGREE' ) can this be done?
Would MYSQL store the different values into the same field?

I'm sat here trying to work this out and would apreciate a little advice. I'm at the early stages of creating this database so I can change things accordingly.
Thanks
Hurst

Report · Jun 16, 2007

On 16 Jun 2007 in macromedia.dreamweaver.appdev, vanroojdotcom wrote:

> Let's say I have to connect single/multiple people to a
> single/multiple logo (representing workmanship qualifications)
>
> They are 'BAFTA' 'NVQ' 'GCSE' 'OLEVEL' 'DEGREE' etc - these will
> appear as images at the foot of the person's personal page...
>
> DAVID has 'BAFTA' 'NVQ'
> MARY has 'NVQ' 'GSCE' 'OLEVEL'
> COLIN has 'DEGREE' 'NVQ'
>
> This is what I have in my head, to show you my problem
> So I create two tables PEOPLE and LOGOS and when inputting the data
> into MYSQLis where I hit a brick wall! Attach the people to the logo
> or the logo to the people? I think its better to input the peron's
> details and then have a dropdown menu of all the logos available and
> then input that. Or maybe a series of dynamic checkboxes that I can
> tick to indicate which logos the person has. But how does this work?
> Are they all stored in a single field on the database "person.logos"
> or do they need to have a single field for each logo
> "person.logos_01[BAFTA]" "person.logos_01[NVQ]"
> "person.logos_01[NULL]" etc etc ? And what would be the actual
> MYSQL code?
>
> INSERT INTO person.logos ( 'BAFTA', 'NVQ' ,'GCSE', 'OLEVEL',
> 'DEGREE' ) can this be done?
> Would MYSQL store the different values into the same field?
>
> I'm sat here trying to work this out and would apreciate a little
> advice. I'm at the early stages of creating this database so I can
> change things accordingly.

You have a classic many <-> many situation here - you're linking many
elements on the left to many on the right.

If those on the right (qualifications, etc) are few, and they don't
change often (or at all), I'd be inclined to make them part of the
person's record:
PersonID: 1
Name: vanroojdotcom
HasBAFTA: T
HasNVQ: F
HasGCSE: T
...

If, on the other hand, the list of qualifications is large, or will
change frequently, you need a three-table design, where the third table
is used to link the other two:

PERSON:
PersonID: 1
Name: vanroojdotcom
Etc: more stuff
PersonID: 2
Name: John Bull
Etc: more stuff

QUALIFICATIONS:
QualID: 1
Qual: BAFTA
QualID: 2
Qual: NVQ

Pers2Qual:
PersonID: 1
QualID: 1
PersonID: 1
QualID: 3
PersonID: 2
QualID: 1

You go on to ask:
> Would MYSQL store the different values into the same field?

MySQL has the 'set' data type which will do what you want; personally,
I've never used it.

http://dev.mysql.com/doc/refman/5.0/en/set.html

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Jun 16, 2007

On 16 Jun 2007 in macromedia.dreamweaver.appdev, Joe Makowiec wrote:

> You go on to ask:
>> Would MYSQL store the different values into the same field?
>
> MySQL has the 'set' data type which will do what you want; personally,
> I've never used it.
>
> http://dev.mysql.com/doc/refman/5.0/en/set.html

More on SET fields:

http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Jun 16, 2007

OK thanks for that - There's only about a maximum of 10 and they will never change. So on the INSERT RECORDSET in Dreamweaver, I would have to create 10 seperate textfields to accomodate the logos (on the Person's ID page) - logo_01 logo_02 etc.

If I create a table for the LOGOS I can have 10 dropdown menus to populate the persons ID with (quicker and to make sure the spelling is consistant) or a Checkbox next to each logo (Y or N).

Have I understood you correctly?

It's a shame you can't have more than one value in a single field.

Thanks
Vanrooj