Blobs

[Diz]

Can anyone please tell me how to decide which blob field to use?
I'm creating a DB using phpmyadmin and mysql. choices are
Tinyblob - maximum length of 255 characters.
Blob- maximum length of 65535 characters.
Mediumblob - maximum length of 16777215 characters.
Longblob - maximum length of 4294967295 characters.
How do i tell how many characters are in the images i want to use? does it depend on the file type (jpeg, gif, etc?) or the actual size of the image.
thanks,
Diz :confused:

[Banfa]

OK firstly I never bother putting images into a DB in that way, I put in a field that contains the name of the image file which can then be used to access the image file directly. This also doesn't have a problem of needing to know what size blob to use.

However should you wish carry on using a blob then you will have to decide the maximum size of image that you want to be able to store. The size of the image is only dependent of the type is so far as the different image types have different ways of storing and compressing the image and so result in a different size. Obviously (I think) the image size is also dependent on the size of the image and it's colour depth, amazingy the more information you wish to store the larger the size of the image file :D

Since you have not said what operating system you are using it is hard to tell you how to find out your image sizes but a couple of methods would be

Windows

In explorer the the view to "Details", the files will be listed with their approximate sizes in kBytes, if you want to know more accurately the right click on a file and select Properties and the files size in bytes will be displayed in the properties box.

On *nix the use of the ls or dir commands should provide the required information.

[Diz]

:p ok, i realise my questions are probably very basic, but give us a break, i'm just starting out!

I will go with your suggestion to link to the images....would that be a varchar field? maybe called link???
i'm also having trouble trying to link my tables together. i am using phpmyadmin and mysql. There isn't an option when creating the tables to specify a foreign key. Any idea how to do it?
thanks for your help,
Diz

[Banfa]

lol, I don't mind basic questions :D

Yep VARCHAR(255) to hold the file name of the image, personally in my implementation I called the field filename but you may call it what ever you want (although I caution against using Supercalifragilisticexpialidocious as it will quickly become irritating to type :p )

OK I kind of need to know how you created your PRIMARY KEY to answer that but assuming like me that you used INT UNSIGNED then you just declare a field with a simlar type in second table.

Since the fields can have any name you want them to it is a good idea to use a naming convention for your PRIMARY and public keys. The convention I use is that the primary key in a table is always called ID and the reference to it in another table is called <TableName>IX but you can do it any way you want.

So you might get something like

CREATE TABLE IF NOT EXISTS CarManufacturer (
  ID           INT UNSIGNED NOT NULL  AUTO_INCREMENT PRIMARY KEY,
  Name      VARCHAR(255)
);
 
CREATE TABLE IF NOT EXISTS CarModel(
  ID                           INT UNSIGNED NOT NULL  AUTO_INCREMENT PRIMARY KEY
  CarManufacturerIX    INT UNSIGNED
  Name                      VARCHAR(255)
 
);
 

When you fill in a CarModel entry you set CarManufacturerIX to the value of the ID field in CarManufacturer. You can then use this in your select statements like so

SELECT CarModel.Name AS Model, CarManufacturer.Name AS Manufacturer FROM CarModel, CarManufacturer WHERE CarModel.CarManufacturerIX=CarManufacturer.ID;

One of the problems of the naming scheme I use is that I can not use the USING keyword when JOINING tables because the USING keyword expects the column name to be the same in both tables.

[Diz]

thanks for replying so quickly. i used INT unsigned zerofill for PKs. (in all tables), but have named them exactly the same in each column. as following:

artwork
Field Type Null Default Links to Comments MIME
artworkID int(10) No
title varchar(25) No
dateCreated year(4) No 0000
category enum('Abstract', 'Figurative', 'Landscape') No Abstract
cost int(6) No 0
forSale enum('Yes', 'No') No Yes


Indexes :
Keyname Type Cardinality Field
PRIMARY PRIMARY 0 artworkID
title INDEX None title
dateCreated
category
cost
forSale

Space usage : Type Usage
Data 0 Bytes
Index 1,024 Bytes
Total 1,024 Bytes
Row Statistic : Statements Value
Format dynamic
Rows 0
Next Autoindex 1
Creation Feb 28, 2006 at 02:33 PM
Last update Feb 28, 2006 at 02:33 PM




artworkmedium
Field Type Null Default Links to Comments MIME
artworkID int(10) No 0000000000 auto
mediumID int(5) No 00000 auto


Indexes :
Keyname Type Cardinality Field
PRIMARY PRIMARY 0 artworkID
mediumID

Space usage : Type Usage
Data 0 Bytes
Index 1,024 Bytes
Total 1,024 Bytes
Row Statistic : Statements Value
Format fixed
Rows 0
Creation Feb 28, 2006 at 02:58 PM
Last update Feb 28, 2006 at 02:58 PM



medium
Field Type Null Default Links to Comments MIME
mediumID int(5) No 00000 medium -> mediumID auto
type enum('Acrylics', 'Charcoal', 'Ink', 'Pastels', 'Pencil', 'Oil', 'Canvas', 'Paper', 'Board') No Acrylics artworkmedium -> mediumID


Indexes :
Keyname Type Cardinality Field
PRIMARY PRIMARY 0 mediumID
type INDEX None type

Space usage : Type Usage
Data 0 Bytes
Index 1,024 Bytes
Total 1,024 Bytes
Row Statistic : Statements Value
Format fixed
Rows 0
Creation Feb 28, 2006 at 02:52 PM
Last update Feb 28, 2006 at 02:52 PM
Last check Feb 28, 2006 at 02:52 PM


So do i need to rename the fields that will be foreign keys?

thanks, Diz

[Banfa]

So do i need to rename the fields that will be foreign keys?

No in all cases the names are arbitary, the concept of foreign keys is just that, a concept. In MySql this concept has no support in the underlying database system it just refers to a way in which you use the data stored in the tables.

The fact you have named your primary and foreign keys the same has no impact ecxept that when doing JOINs you can use the USING keyword.

For example, using the database structure you have provided, the following 2 select statements should return the same result

SELECT title, type 
              FROM artworkmedium, artwork, medium 
              WHERE artworkmedium.artworkID=artwork.artworkID 
                 AND artworkmedium.mediumID=medium.mediumID;
 
SELECT title, type 
              FROM artworkmedium 
              LEFT JOIN artwork USING(artworkID) 
              LEFT JOIN medium USING(mediumID);
 

Because you have used the same name for your primary and foreign keys you can use both of these methods, because I use ID for primary keys and <TableName>IX for foreign keys I can only use the first of these 2 methods (in hindsight a possible mistake on my part but I am a C programmer and I named the fields in a programming manor rather than a database manor).



On your table structures I notice that the cost is in the artwork table, however I would have expect that a given piece of artwork produced on 2 different mediums would have 2 different costs (because it will be more expensive to reproduce on 1 medium than the other). In this case shouldn't the cost go into the artworkmedium table?

[Diz]

artworkmedium table is there solely to join the other two tables together. this is because each piece of artwork can be made up of more than one medium- eg pastels and oil on board. so i need to select three option from the medium table. Each piece of artwork is individually priced, so all works on canvas may be more expensive than works on paper, but the price will vary from painting to painting, hence the cost is in the artwork table...thanks so much for your help with this, you've made it much clearer :)