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?

Recommended Answers

All 4 Replies

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

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

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?

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

pic_table <---- why not have photographer here

what about random pics that not actually take at an event?

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

any reason not to put relationships and rolls in the same table?


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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.