SQL - Creating and searching ENUM types...

seamus

Right, basically what I'm looking to do is set up a members section on a website. Members can then subscribe to things, and each thing has an ID, which is the index in it's table.
E.g., like the boards Forum subscription, each member would have a field in the DB called "subscribed_forums", with a list of values like "1, 34, 53, 67, 350" with each number corresponding to a forum id.

So basically I need to know how to create an ENUM type that lets me use a range of numbers (and be flexible enough to expand, should more ids be needed), and how then to search through them, ie "SELECT * FROM user WHERE forum_id = 31"

Kev

why do you want to do that ?

you should create a new table with each row being a member id and thing id pair.

seamus

Originally posted by Kev
why do you want to do that ?

you should create a new table with each row being a member id and thing id pair.

You mean a separate table with only two fields, e.g. user_id and forum_id?

And so to get a list of subscriptions, or vice-versa, run a query like "SELECT forum_id FROM sub WHERE user_id=x"?

Never thought of that
Would that not begin to get very large and put big loads on the server if/when the user list gets big though?

sceptre

Originally posted by seamus
Would that not begin to get very large and put big loads on the server if/when the user list gets big though?

Not really. It'll take a bit more processing but not a substantial amount.

one of the important parts of "good" database design is to have as few empty fields as possible

Evil Phil

Back of Enums. Do you want an Enum that gives you each members 'subscribed_forums' list? As in:

Enum = 7, therefore member is subscribed to a, b, and c.

Enum = 5, therefore member is subscribed to a and c.

seamus

Excellent.

Cheers lads

I see what you mean EvilPhil, but I think the other solution will suit better, thanks.

bonkey

Originally posted by seamus
You mean a separate table with only two fields, e.g. user_id and forum_id?

That would be the standard way of implementing a many-to-many relationship in a relational database.

Set up Foreign Key Constraints (sometimes called referential constraints) to prevent the entry of invalid user_id or forum_id values.

Would that not begin to get very large and put big loads on the server if/when the user list gets big though?

Depends what you call big

10,000 users each subscribed to every one of a total of 100 forums would result in 1,000,000 rows. However, with proper indexes in place (i.e. one on user_id, one on forum_id) thats still a trivial amount of data.

I dunno what DB you're thinking of using, but off the top of my head...

With the numbers of users/forums/subscriptions I gave above SQL Server 2000 (using 8K pages, as it does) would find any single user/forum's subscribers with 2 or 3 logical reads of the index pages (physical reads if the data is not in a cache) followed by as many reads as were required for the data itself. Given that you will always have to read the data, regardless of how you find it (i.e. via indexes, or by using another structure) , thats not a significant amount of work.

Even older DBs which used predominantly 2K pages would require a max of 4 index-page reads to get to the first record. Still trivial.

jc

Typedef

Yes

SELECT fields(n) from user_subscribed_fora where uid='integer';

no

Else.

amen

the other solutions are better but you could always define a
value such as 00000000
and turn on a value ie 00100000 which could mean forum 4
or even do a XOR on it

bit messy though and doesn't lend it self to adding new forums