Image Database Design

[fife]

Ok ill try and describe this best I can.

 

I have a members table, a company table and a link table between the two as a member sets up a company. Also there will be a permission table as other members can be part of that company based on their permission.

 

 

Members company link_mem_com permission

idmembers idcompany idlink_mem_com idpermission

etc etc idmembers p_name

idcompany

idpermission

 

 

 

 

 

Now Im not sure how to design the next bit. Basically a member will have an area for uploading pictures. However so will a company. Then on top of that other members can be apart of that company and also upload pictures.

 

here is my thinking for the table structure for this feature.

 

I will create an images table with uploader_id and type. i_type will be 1 for member and 2 for company. Then I will create a tag table so a member can share an image with other members and the companies they are part of.

 

Now. I have no idea if this is right or wrong. Can someone please help me with how to do this properly or tell me if this is a suitable option?

 

image tags

idimage idtags

uploader_id idimage

i_type i_type

name uploader_id

filename

i_status

 

 

Thanks All

[Psycho]

Is uploader_id supposed to be the user who uploaded the image or what 'object' it is associated with? If the latter, then you absolutely need a field for associating the image with the company or user.

 

I'm not sure I understand how your "Tags" table is supposed to work. I'm *guessing* that your intent is that the type and uploader_id are the type and ID of what the image is shared with. I think that would work based upon your description. But, your field names are counter-intuitive to me based upon what I think they are supposed to mean.

 

On second thought, you should make one change. In the images table don't use the i_type and uploader_id at all. Just put that information in the tags table - even for the original member/company the image was uploaded for. If you have a specific need to know what member/company the image was first uploaded for, then add a column in the tags table to identify that:

[b]mage[/b]
------------
idimage
name
filename
i_status

[b]tags[/b]
------------
idtags
idimage
i_type
uploader_id
originator (set to 1 if this was the original entity the image was uploaded for, else 0)

 

The way you originally had it would require two sets of JOINs - one on the uploader ID in the images table and another on the tags table

[fife]

Awesome thanks very much for the reply and thank you for your advice. I will try that. I think your way would be much better as its less data stored in the tables and like you said. I will be able to get to my end result much quicker.

 

many thanks

 

Danny