Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    30

    Normalization..........

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by boing
    What is the best table structure for me to create some tables so that i get no repeated data?
    that depends -- can an image belong to more than one customer, or only to a single customer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    30
    only to a single customer, thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create table images
    ( img_id primary key
    , cust_id foreign key references customers (id)
    , image_data ... )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    30
    Thanks, so its ok for the for foreign keys to be duplicated?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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 ... )

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loquin
    ... performance reasons ... client-side cursor application
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by loquin
    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.
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    fairy 'snuff
    some users and their odd ideas.........
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loquin
    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.
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •