Plan a SQL database with pictures etc

[lala]

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

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]

Originally Posted by melkior

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]

Originally Posted by lala

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

Originally Posted by ForumJoiner

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

[lala]

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

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.

[lala]

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

Originally Posted by lala

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.

Originally Posted by lala

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.

Originally Posted by lala

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:

PHP Code:

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.

PHP Code:

SELECT * from products WHERE id="$id"


Originally Posted by lala

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.