Hi,

I am in a bit of a mess with some tables i am creating. I have a customers table which contains information about them - name etc. But i also need to be able to upload some pictures to do with each customer. So i need an images table.

The images cannot be in the same table as the customers details.

However if i have a table with customerID and imageID i will get duplicate data which is bad as it could be like:

CustomerID ImageID
0001 im001
0001 im002

What is the best table structure for me to create some tables so that i get no repeated data?

Thanks

that depends -- can an image belong to more than one customer, or only to a single customer?

only to a single customer, thanks

create table images
( img_id primary key
, cust_id foreign key references customers (id)
, image_data ... )

Thanks, so its ok for the for foreign keys to be duplicated?

not sure i understand what you mean by "duplicated"

here are some customers --

2 fred
3 barney
4 wilma

here are some images

42 2 [image of fred]
43 2 [image of fred with betty]
44 4 [image of wilma, very angry]
45 5 [image of barney, also p1ssed]

there is no "duplication" except insofar as fred has 2 pictures, and each one links back to fred's customer row

And if a customer is only allowed 1 image then:

create table images
( img_id primary key
, cust_id unique foreign key references customers (id)
, image_data ... )

or even

create table images
( cust_id primary key foreign key references customers (id)
, image_data ... )

agree, except if the image is to be unique(ie one image per user / parent record , then there seems to be little point in storing it in a subtable, unless you are contstrained but either column limits, design limits (ie no design rights to the parent table))

a third option would be to define images and a separate table which cross references images to persons

to extend Rudy's example

2 fred
3 barney
4 wilma
5 betty

here are some images

42 [image of fred]
43 [image of fred with betty]
44 [image of wilma, very angry]
45 [image of barney, also p1ssed]

here are some cross references (image to person)
42 2
43 2
43 5
44 4
45 4

It could be useful to store the image in a separate table for performance reasons in a client-side cursor application.

I've an app where I store the thumbnail in the main table and the full-size image in a separate 1:1 table. I only retrieve the full-size image when needed.

that sounds like an oxymoron to me

could you please take a moment to explain what that means and why it is better performance than, say, a client side non-cursor application, or a server side cursor application, or even a server side non-cursor application

thanks

personally in that circumstnace I'd store a URL to the thumbnail, and a URL to the full size image, and display which ever version was required for the user interface. in part thats because i dislike the idea of storing essentially static information in a db, and boosting the backup tapes for no (imhv) good reason.

Well, I dislike using Access, but in one case I was required to use it to store images within the database. (I couldn't disuade the end user to use a read database server, or to store the UNC pathname in the in the database)

I needed to display the thumbnail on a form and load the actual image on user demand.

If I kept both images in the same table, whenever I load the recordset, all the full image will be copied from the datafile to the client when the record is displayed, even if it isn't needed. However, by employing a 1:1 relationship, I only needed to load the full image when the user requests it.

fairy 'snuff
some users and their odd ideas.........

there's your mistake, you were probably using the evil "select star" and of course then you deserve everything you get, dude

i still don't see how client side cursoring is a performance gain, or even what it means

more explanation please!!