[Zimwy]

Hi,
I'm designing a database web site for a music camp. There were a few problems I came across that I'm somewhat found answers to but I'm not sure they're the best ones, so I figured I'd ask here. Here's the situation. I have a table of Groups, and each group can have an arbitrary (2 -> 100) number of people in it. There is also a table of People each of whom have ID numbers. What I did, was put a varchar inside each Group, that just holds comma-delimited id numbers of the people in the group, and then I use PHP to separate it out and figure out which people those are in a separate SQL query.

This is only bad for two reasons:
1) I do this for many things, one of which is the movement names of a piece. A piece can have an arbitrary number of movements, so I just tab delimited them like before. Unfortunately, the movement names themselves can't have commas in them.

2) Varchars can only get 255 characters long it seems. Is there something else I can use that will expand bigger? For pieces with many movements, the total length of all movement names quickly grows over 255 characters.

Thanks for your help!
gabe

(edit: spelling)

[larkost]

Ok... first off I would recommend picking up a basic book teaching database design... it sound like you need an intro to the general practices... I am going to give you pointers in the right direction here, but without a good grasp of the basics you are going to wander off course very quickly.

You should really have three tables here: Person, Group, and PersonToGroup. Here is an example layout:

• Person (with id and Name columns)
• Group (with id and Name columns)
• PersonToGroup (with person_id and group_id columns)

Now to get a list of people in group.id 12 you call:

Code:
select Person.id, Person.name, Group.Name
from Person, Group, PersonToGroup
where Person.id = PersonToGroup.person_id and PersonToGroup.group_id = Group.id and Group.id = 12

. This method is very flexible when you start doing fancy things, and is just a good habit to get into. The same sort of thing can be applied to fix some of the problem you are running into with the movements problem. The rest can be solved by looking at MySQL's documentation about varchar (and other character type) columns on their site (*hint* you set up your column wrong).

editied to make the SQL wrap better

[skalie]

Originally posted by larkost:
Ok... first off I would recommend picking up a basic book teaching database design...

.... was there a particular one you were thinking of?

[larkost]

Originally posted by skalie:
.... was there a particular one you were thinking of?

Unfortunately mine had a college professor attached to it, so I didn't really pay much attention to the book... and I think that the professor in question has some (reasonable) questions about how much attention I paid him... but a lot of it seems to have stuck.

[Simon Mundy]

Originally posted by Zimwy:
Hi,
2) Varchars can only get 255 characters long it seems. Is there something else I can use that will expand bigger? For pieces with many movements, the total length of all movement names quickly grows over 255 characters.
(edit: spelling)

...and the answer you were probably looking for is a field type of TEXT instead of VARCHAR. You can also split hairs and have TINYTEXT (which is for all intents and purposes the same as VARCHAR(255)), MEDIUMTEXT, TEXT or LONGTEXT. But TEXT should do you for your purposes.

I hear you Larkost for most problems, but in some cases it simply isn't worth adding yet another table for something that can be handled with arbitrary data - like weekday names or month names or status flags. It's much easier and terser to have a field called 'weekday_active' with a value of 'mon,tue,wed' rather than abstract it yet again with another table/lookup.

OTOH, Zimwy's design has one major flaw - whenever you delete a person from the person table, you have redundant data in your group table (where the comma-seperated data of people IDs are kept) unless you manually perform some calculations to refresh those values. If you had a table type of InnoDB (or another database that supports foreign keys) you could assign foreign key lookups on the person.id fields in Larkost's example and it would automatically delete 'redundant' fields if you programmed the keys that way.

Database design is fun isn't it?! I found that a good intro to PHP and MySQL is the book by Welling and Thomson called (unimaginatively) PHP and MySQL. It's a great read and a good start. Once you start learning more you can use it to prop up wobbly tables, but for the start it's indispensible.

BTW, Zimwy, you can perform a search for a group simply by:-

SELECT group.id, group.name WHERE FIND_IN_SET(person_id,group.members)

where your 'group.members' field is a comma-seperated data field of your person id's.

[Zimwy]

Originally posted by Simon Mundy:
BTW, Zimwy, you can perform a search for a group simply by:-

SELECT group.id, group.name WHERE FIND_IN_SET(person_id,group.members)

where your 'group.members' field is a comma-seperated data field of your person id's. [/B]

Wow great. Thanks! FIND_IN_SET is a predefined SQL keyword? Luckily enough for me, you will never delete people from the database. FIND_IN_SET does just what I think it would? That is if my group.members is like "10,3,2,6" it will just look for whatever person_id is inside that set?

Thanks!
gabe

[Simon Mundy]

Originally posted by Zimwy:
Wow great. Thanks! FIND_IN_SET is a predefined SQL keyword? Luckily enough for me, you will never delete people from the database. FIND_IN_SET does just what I think it would? That is if my group.members is like "10,3,2,6" it will just look for whatever person_id is inside that set?

Thanks!
gabe

That's right - if you wanted to search for all groups with a person_id of 3 then it's:-

SELECT group.id, group.your_field_name FROM group WHERE FIND_IN_SET(3,group.members)

Hopefully this works well enough for your needs. But like Larkost said, it's a good idea to read both the online MySQL docs (people have contributed interesting notes and addendums to the docs) and some third-party material on the idea of not just the syntax but the black art of designing databases as well.

Cheers

[larkost]

Originally posted by Simon Mundy:
[BI hear you Larkost for most problems, but in some cases it simply isn't worth adding yet another table for something that can be handled with arbitrary data - like weekday names or month names or status flags. It's much easier and terser to have a field called 'weekday_active' with a value of 'mon,tue,wed' rather than abstract it yet again with another table/lookup.
[/B]

Isn't that the whole reason behind the ENUM type? That places it in as bit field, and thus is much smaller, and you never have to use a text parser, so it is much faster. This is useful when you know all of the settings from the get-go and you don't have more than 16 (32 at the outside) items to list. After that a bridge table really is the way to go.

In the case we are talking about a bridge table really is the right solution. It requires that you understand something about relational databases... but... why is that a bad thing?

[Simon Mundy]

Originally posted by larkost:
Isn't that the whole reason behind the ENUM type? That places it in as bit field, and thus is much smaller, and you never have to use a text parser, so it is much faster. This is useful when you know all of the settings from the get-go and you don't have more than 16 (32 at the outside) items to list. After that a bridge table really is the way to go.

In the case we are talking about a bridge table really is the right solution. It requires that you understand something about relational databases... but... why is that a bad thing?

I think you may have ENUM confused with SET as the ENUM can only hold one value at a time whereas SET can hold multiple values. The limiting factor for SET is that you can only define 64 individual values and they need to be preset into the table structure. It is sometimes more flexible and just as efficient to use a TEXT field for certain purposes.

An instance where this scenario worked well for me was with the Postfix/MySQL vacation responder from www.high5.net - Mischa has implemented a 'cache' field in the vacation autoreply table that stores all the email addresses in CSV format, and then his script simply performs a FIND_IN_SET on the incoming email address. Sure, you could also perform a 'WHERE email LIKE '%email@email.com%' but that's less exact than specifying a delimiter with a comma (because I could mistakenly match 'femail@email.com.au' by accident!). Secondly, there is no need for me to create a bridging table as I have no need to store any other information about those email addresses (IDs, etc.). This 'cache' field is either going to be empty or full and I don't need to worry about redundant data as the lifespan of this field is only 2-3 weeks at worst in most cases.

Again, though to play Devil's advocate, if you use a TEXT field for storing lookups then you miss out on MySQL's speedy indexing for one. So that should be a deciding factor in the table layout.

It's not a bad thing to know more about your tools - I agree wholeheartedly! If we can thoroughly explore the options and the pros and cons then we can ultimately find the most suitable methodology for the job. Time vs Complexity vs Scope, etc...