Tagging system set up - which is better between these two?

[felgall]

Originally Posted by shaunthomson

Thanks makes sense. Thanks Felgall.

Can you use three columns that are unique together as the primary key, or is that not workable?

If a table is just one column, how can it relate to any other tables - how do you create a relationship?

You can have three columns in a key.

A one column table would need to have its column as part of the key in the other tables that refer to it. Its purpose would be to limit the values that can be contained in that column in the other table(s) to the values in the one column table.

[Old Pedant]

Originally Posted by shaunthomson

Can you use three columns that are unique together as the primary key, or is that not workable?

*SIGH*

Didn't we have this discussion already.

Didn't we talk about "skipping" fields in multi-field indexes?

Didn't I even suggest that a good primary key would be

CREATE TABLE djBooks (
    djid INT,
    songid INT,
    bookNum TINYINT,
    PRIMARY KEY( djid, booknum, songid )
);

And didn't I note the reasons for putting the fields in that order in the (yes, three field) primary key?

I even gave the examples:
-- a WHERE involving just DJID will use the index
-- a WHERE involving DJID and BOOKNUM will use the index
-- a WHERE involving all three fields will use the index
BUT:
-- a WHERE involving just BOOKNUM will *NOT* use the index
-- a WHERE involving just SONGID will *NOT* use the index
-- a WHERE involving just BOOKNUM and SONGID will *NOT* use the index
AND:
-- a WHERE invovling just DJID and SONGID will use the index only for the DJID.

(All the above may not be true in other databases--SQL Server in particular--but it is a well document limitation of MySQL.)

[Old Pedant]

Originally Posted by felgall

A one column table would need to have its column as part of the key in the other tables that refer to it. Its purpose would be to limit the values that can be contained in that column in the other table(s) to the values in the one column table.

An reasonable is a simple lookup table. Say something like

CREATE TABLE AvailableColors( 
    color VARCHAR(20) PRIMARY KEY 
);
INSERT INTO AvailableColors 
VALUES('Red'),('Blue'),('Black'),('White'),('Pink polka dots');

[felgall]

Originally Posted by felgall

A one column table would need to have its column as part of the key in the other tables that refer to it.

Just to clarify - it doesn't have to be part of the primary key (although it would be in a join table used to attach multiple values), it could be a foreign key (in a table where exactly one of the values is required)..

[shaunthomson]

@Felgall - thank you mate - I get it now

@Old Pedant - you're quite right - that was a lazy question