473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Blobs

19
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:
Mar 1 '06 #1
6 1803
Banfa
9,065 Expert Mod 8TB
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.
Mar 2 '06 #2
Diz
19
: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
Mar 2 '06 #3
Banfa
9,065 Expert Mod 8TB
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

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE IF NOT EXISTS CarManufacturer (
  2.   ID           INT UNSIGNED NOT NULL  AUTO_INCREMENT PRIMARY KEY,
  3.   Name      VARCHAR(255)
  4. );
  5.  
  6. CREATE TABLE IF NOT EXISTS CarModel(
  7.   ID                           INT UNSIGNED NOT NULL  AUTO_INCREMENT PRIMARY KEY
  8.   CarManufacturerIX    INT UNSIGNED
  9.   Name                      VARCHAR(255)
  10.  
  11. );
  12.  
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.
Mar 2 '06 #4
Diz
19
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
Mar 2 '06 #5
Banfa
9,065 Expert Mod 8TB
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

Expand|Select|Wrap|Line Numbers
  1. SELECT title, type 
  2.               FROM artworkmedium, artwork, medium 
  3.               WHERE artworkmedium.artworkID=artwork.artworkID 
  4.                  AND artworkmedium.mediumID=medium.mediumID;
  5.  
  6. SELECT title, type 
  7.               FROM artworkmedium 
  8.               LEFT JOIN artwork USING(artworkID) 
  9.               LEFT JOIN medium USING(mediumID);
  10.  
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?
Mar 2 '06 #6
Diz
19
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 :)
Mar 2 '06 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Ole Hansen | last post by:
Hi, Is it at all possible to insert BLOBs using the Array Interface? Today I have an application using the array interface. It works fine but so far I haven't been using BLOBs. I insert...
7
by: Howard Lowndes | last post by:
My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2 I have no difficulty inserting and managing BLOBs into the Large Object system table, and I have a user table called images...
7
by: Nilabhra Banerjee | last post by:
Hi, I am still not sure whether the BLOBS are actually stored in the database or they have the pointer to the database for that file in the filesystem. If I remove the files (sources) for BLOBS...
0
by: Bing | last post by:
Hi there, I am using the DB2 universal JDBC driver type 4 to insert BLOBs into a DB2 database. The Method I used for supplying the BLOB data value is setBinaryStream(). Everything works fine as...
2
by: Jerry LeVan | last post by:
Hi, I am just getting into large objects and bytea "stuff". I created a small db called pictures and loaded some large objects and then tried to do a restore. Here is how I got the dump. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.