Design Structure?

bjblackmore

Hi,
I'm a bit stuck, and I was wondering if someone could provide some help. I have a website which will have about 100 pages for different people, each person gets certain images on their page. I want to put a code for the images in a database, should I put the code in 1 field? and how should it be constructed? If I have 5 images, A,B,C,D,E. And someone gets iamges A,C & E. Should I put ACE in the database? or A,C,E. Or should I add a separate field for each image? Trouble is each person will have a different number of these iamges, so will have 1, some will have all 5. The database so far is..

user_id|user_name|description|address|

and then i want to add the image field. Is there a better way of doing this? I was going to add a function
function get_image()
if (image=='a') { echo '<img src="a.gif">';}
if (image=='b') { echo '<img src="b.gif">';}
etc...or something
Cheers
Ben

ace21

put them all in one field seperated by a special character like ~

then you can use "implode" and "explode" to get an array of different pics or to make a string from an array to store in the database.

then you can use the "count" function with the array to determine how many pictures need to be displayed.

This will help if you decide to have more pics later because you don't have to add new fields to the database.

Ace21

bjblackmore

Ah ok, excellent, I'll have to read up on implode/explode.
Thanks for the help
Ben

Anon

When defining a template structure for a site, I used a single database field with the different page elements encoded as binary bits. It is then simple to use the '&' operator to see if any given element is included:

for example:

A=1, B=2, C=4, D=8, E=16

So, if you want to display the images ACE, the value you'd encode in the field would be 1+4+16, or 21, which is guaranteed to uniquely identify the set of ACE.

Because you're dealing with a single number and bit operations are very fast, it works very well.

miasma

There's a quick and dirty way to do this as specified by putting it in one field and parsing that field.... but if you wanna know the proper way of doing it.... here's my take on it.

You want to normalize your database. There's a very good article here at phpbuilder about that, i'd find the link but i'm lazy =).

Normalizing involves separating out your data so that you don't have duplicate data or have to manipulate it in some way to get what you want.

In this case, I would have two tables in your database for this.

Your first table is fine:
userID|username|description|address

You'll then want a second table that's like this:
userID|image

This way, you can have multiple entries in your second table for each userID... some users will have 2 rows in the second table, some will have 5, etc... you pull it together through the userID.

I hope this helps.

Anon

right on, miasma. further to this, you are setting up a many-to-many relationship.

Here is roughly how I would set it up (with three tables):

Users
UserID
Name
...

Images
ImageID
path
name
...

relUserImages
UserID
ImageID

Since tables have a one-one or one-many relationship, you need an intermediate table to setup the many-to-many relationship between Users and Images.

Anon

Just depends on what you're doing. Joins can be quite slow and for a high-traffic site should be minimized as much as possible.

There is nothing intrinsically wrong with 'manipulating' data, especially if it results in a better product.

And that's not why you normalize anyway.

Remember Occam's Razor.