sql question

[Journer]

so lets say i have a table call stuff
stuff has 3 columns, name, date, and picture
lets say there is a row that requires more than one picture

name1-date1-pic1
pic2
pic3
name2-date2-pic1

how do i accomplish this without creating more data? should i create a separate table called photo album or something like this?

table1
name1-data1-album#1
name2-data2-album#2

photoalbum
#1- pic1- pic2 - pic3
#2 - pic1

but even this limits me to how many picture columns are in the photo album table

how can i set it up to where an item can have a variable amount of pics for a single item?

 

[WannaFly]

Read up on table normalization (normalisation?) - It's a staple in good database design. Also Primary/Foriegn Keys

What you want is:
Table: Albums
Columns: Album ID(auto inc, PK), Album name, date, etc

Table: Pictures
Columns: PicName, Date, AlbumID (Foriegn key to Ablums.ID)

 

[Journer]

ok that makes total sense now...so if i wanted to list all the photos from a table's pics it would be something like

select * from pictures
where album id = (select albumid from table where name='1')

i just forgot that third table. we did some normalization in my DB class but i never quite understood it fully...and what the purpose of the different forms were (1st normal, 2nd, etc.) so i suppose i should read up on it.

also, if one item in the main table only had one photo album, would the photo album table really be necessary? could'nt it be something like

table1
name1-date1-uniquealbumID

pictures
pic-description-uniquealbumID(fk)

select *
from pictures
where uniquealbumID = (select uniquealbumID from table1 where name='name1')

...no?

 

[MrChad]

This query

select * from pictures
where album id = (select albumid from table where name='1')

should be written as:

SELECT pictures.* FROM pictures, table WHERE pictures.ALBUMID = table.ALBUMID AND table.NAME = '1'

or

SELECT pictures.* FROM pictures INNER JOIN table ON pictures.ALBUMID = table.ALBUMID WHERE table.NAME = '1'