Plan a SQL database with pictures etc

[lala56]

Is there any "clever" way for planning the stucture of your database. I know you should avoid redundant data as much as you can and kind of cross-reference related items in different tables. But is there any good way of forecasting how future requirements might look like in order to avoid a complete re-structuring of you database?

Also, I always wondered how you store pictures in a SQL database.

lala

 

[melkior_inactive]

Well, the best way to design the DB is to get all the data you will be using on a paper.
When I create web apps, the first thing I do is design the database. I find it easier to develop the application when I have a data overview. Then I know what functions I'll need and what parts the app will have.
I lay out all the data I'll be using and manipulating and try to organise it in tables the best way I can. I sometimes draw dataflows and workflows to get the a better view of what I'm actually doing.
Basic structure would be users table, products table, other tables.
I like keeping the settings in files, not in the database.
So if I want the images for products, I'd add a column in the products table and name it images, or something.
To store images, you use BLOB.
It's a datatype - Binary Large Object.
Now, it's usually recommended not to store images in the DB, but I'm running a site that had 13GB (not a mistake, it's gigabytes) of pics stored in BLOBs so you shouldn't have any problems.
If you need more help, post more specific questions and I'll see what I can do.
If you want to avoid storing images in the DB, you could upload them to a folder, and store the path to the pics in the DB instead.

 

[ForumJoiner]

I'm running a site that had 13GB

If you don't mind, what web host allows you a database of 13 GB? Or, do you have your own server? I'm asking this because, in the future, I might need a huge database.

 

[ForumJoiner]

I know you should avoid redundant data as much as you can and kind of cross-reference related items in different tables.

If you are asking also how to avoid redundancy, I believe this article might be a starting point:
Database normalization - Wikipedia, the free encyclopedia

 

[melkior_inactive]

If you don't mind, what web host allows you a database of 13 GB? Or, do you have your own server? I'm asking this because, in the future, I might need a huge database.

It's a dedicated server. Talk to Temi for more info.

 

[UK WW ex member]

No clever way needed. Follow the principles of designin database . Normalization is a database concept to avoid redunancy . Read about database normalization it will help . yes Blob data type will allow you to store image in the database

 

[lala56]

Thanks to all of you You have been very helpfu.

I will start my project in due course and might come up with some more questions (if you do not mind)...I am very glad that it is possible to store pics in the database. Or, do you think it would be more efficient to store the path? What would be the preference?

Sorry, I did not come back to you earlier. My son was terribly ill, so I was a "24/7-nurse" with no time for my projects...Actually, any time I do not get back immediately to you guys this is usually related to some sort of a 2-year old disaster

lala

 

[melkior_inactive]

If you're planning to have more than 1GB of pictures in the database, store paths only. If less, you can store the pics directly in the DB.
And no problem if you have more questions. Glad to help ya!

And I'm glad you're son is better.

 

[lala56]

Thank you Melkior

Actually, I have few other questions related to the same project, which is a website for a friend's furniture business: At the left side we would like to have the navigation for the furniture categories. We would like to have them categorisied by range and also underneath by room.

For that purpose would it be sufficient to have a column with range and another one with room in the product table of the SQL database? The navigation bar should take the values, which are stored for range and room in the database (obviously only once). On top of that the main page's content should update accordingly (range or room overview pages will have pics with name underneath of ranges or possible items in room).

Where I do not get my head around is especially if it is possible to have one PHP file for the range or room overview, one for a choosen range or room and one for the choosen item, which update themselves accodingly to what the user clicked on (to save me from hard-coding files for each possible combination or product, for example 50 item pages if we would have 50 products).

I am very new to PHP. Have actually only read a book about it without doing much with it yet. Really eager to get the grip on it, but will certainly pose some really basic questions. I hope you do not mind...

lala

 

[melkior_inactive]

Thank you Melkior

Actually, I have few other questions related to the same project, which is a website for a friend's furniture business: At the left side we would like to have the navigation for the furniture categories. We would like to have them categorisied by range and also underneath by room.

OK, fair enough. That doesn't seem to complicated, so I'm with ya so far.

For that purpose would it be sufficient to have a column with range and another one with room in the product table of the SQL database? The navigation bar should take the values, which are stored for range and room in the database (obviously only once). On top of that the main page's content should update accordingly (range or room overview pages will have pics with name underneath of ranges or possible items in room).

Yes, those 2 columns should be enough. If your SELECTs are written properly the page will update itself accordingly every time it's opened.

Where I do not get my head around is especially if it is possible to have one PHP file for the range or room overview, one for a choosen range or room and one for the choosen item, which update themselves accodingly to what the user clicked on (to save me from hard-coding files for each possible combination or product, for example 50 item pages if we would have 50 products).

No, you don't need to create hundreds of pages. (You could of course, but that kills the whole purpose of PHP, and it's a little bit masochistic )
What you do is load all external files through index.php
For instance if you have 3 files (let's put them in the includes/ sub folder) you need to send them using the GET method.
So it would look like (if the names of the files are room.php, range.php, item.php) index.php?query=room or index.php?query=item
and to get to a specific item or room or whatever the adress would look like index.php?query=room&id=bedroom
That would list the furniture available for the bedroom.
So the SQL select would look something like:

SELECT * from products WHERE room="$room"

A specific item would be something like:
index.php?query=item&id=37
So that would retrieve an item that has the ID 37. This would be the theoretical select.

SELECT * from products WHERE id="$id"

I am very new to PHP. Have actually only read a book about it without doing much with it yet. Really eager to get the grip on it, but will certainly pose some really basic questions. I hope you do not mind...

lala

PHP is fairly easy. Once you get the logic and the basic idea you can always look up the correct syntax on the internet.
I haven't read a book on PHP in my life. I just took a few applications apart, to get the basic idea and logic and started writing my own apps.

 

[lala56]

Melkior,
Thanks again for your great help...

I still have the same blockage in my brain : How do I know what to query when I cannot anticipate what the user clicks on? He/she could click on the furniture by room selection, choosing "Living room" for example and then a specific sofa, but he/she could also go to the furniture by range selection, choose a range and then a different item...This where I really get stuck...

Great that you learned all the stuff without any books or so. When I start sth my confidence is usually very low, so I invest in a book with the outcome that I read the whole thing and then forget about it

The ever so tired lala (too much paper work today )

 

[smudgeit]

13GB Databases ... LOL Makes backing up a chore seen as I like to use blowfish to encrypt the backupfiles .