Have I really Got This Right?

[J.C. SolvoTerra]

Hi guys, I'm currently on the pretty cool Database development path. I've quickly got to terms with basic SQL with individual tables, and various queries. Now things seen to be getting a little beyond a concept I had been taught from a young age.

Am I doing this right. The images are in Excel for display purposes but this reflects my modal.

I then have a tables which holds data like who the subjects are and who the focal subject is

However in this TagTable I am repeating entries eg. idPeople 3 (Rachel) she is a bridesmaid and a musician, so she has two entries. idPeople 5 (Lorna) has three entries because i've classed them as Mother, PArent and family. Not only that, all these entries are for one photo (idPhoto9)

1st, Guys Am I Doing This Right Before I Get Too Deep?

If I Am Getting Things Roughly Right Then,

lets say, I want a table with the bridesmaid at the firstdance. would my query be something like a JOIN INNER to compare results from the TAG table and ALBUM table. What I think I do is get a results table which consits of all images including the Bridesmaid, I then query that results table with a JOIN INNER query against the ALBUM table to return results that only have the bridesmaid in the first dance event?

Final Question

Now I want to return all photos at location Chapel. Chapel has 5 Event sub categories. Do I have to requery 5 times for each of the sub categories?

[jstfsklh211]

looks good

you may want to consider a category table for search purposes ie firstdance/friends/bridesmaides

albums would be likely to contain pics from multiple categories

also i would break the photo out of the album table and use a crosswalk table between album and photo
you want to be able to do an album search without looking at photos

[J.C. SolvoTerra]

Is the category table you talk of not my "Event Table" in the model?

The crosswalk bit (I'm not sure what that is) but is it something like:

    idPhoto     idTag
    9           1
    9           2
    9           3
    9           4
    9           5
    9           6
    9           7

[J.C. SolvoTerra]

I think The tables may be missleading. The Album Table is the main table which will be queried when looking for photos eg

SELECT idPhoto FROM Album WHERE idEvent = 8 (First Dance)

then to get people tagged

Select idPhoto, idPeople From Tags Where idRoll = 4 (Bridesmaid)

I then think id do the JOIN INNER thing with these two tables to get images of only the bridesmaid at the first dance?

[jstfsklh211]

missed the event table last time
assumed you were doing actual albums

pic_table <---- why not have photographer here
id|filename|idphotographer|......

what about random pics that not actually take at an event?
categoryTable
id|description|location_id

pic_categoryTable(crosswalk table) old album table
naming convention helps remember what things really are
also you may want an album table later on
id|pic_id|category_id

any reason not to put relationships and rolls in the same table?
people_roll/relationshipTable
id|people_id|roll_id

tagTable
id|people_id|pic_id

select stuff from pics
inner join pic_category on pic_id=pic_id
inner join tag on pic_id=pic_id
inner join people_roll on people_id=people_id
where roll is bridemaids
and category is firstdance
for bridesmaids @ firstdance