Is it good practice to keep image file extension in a seperate table?

[rebeldeveloper]

Hi,

I am a php/mysql newbie and in my process of improving my skills i am working on a ecommerce personnal project. I am trying to change the structure of my db to meet the standard techniques.

After working with this db design for product…here it is:

product table
id product_name product_price product_desc product_img groupeid
1 bag red 2000 bag red cool bag_red.jpg 2

So i want to update the product table to: option1

id productname productprice productdesc product_img product_imgExt groupeid
1 bag red 2000 bag red bag_red jpg 2
2 shoes yellow 1000 nice shoes shoes_yellow jpg 1
3 Tie yellow 1000 nice Tie tie_yellow png 1

option2

img_ext table
id img_extention
1 jpg
2 png

product table
id product_name product_price product_desc product_img groupeid
1 bag red 2000 bag red cool bag_red 2
2 Tie yellow 1000 nice Tie tie_yellow 1

---

product_img_table
product_id img_ext_id
1 2
2 1

Which option is better to follow: the option 1 or option 2?

[JimL]

Do you expect the extension to change and/or do you want to query for spesific extensions? If not there’s no need to separate the extension into its own column. I’d move the images into a separate table though. I’m also very against using column naming like productname, productprice, etc. First of all it’s all redundant when you add in the table (product.productname), second it leads to abbreviations, like you have in product.productdesc, third it leads to problems with naming (your columns are some with, some without underscore)

I’d consider alternative 3

group_product
group_id, product_id

product
id, name, price, description

product_image
product_id, image_id, main (maybe, bool to set which image is the main/cover of the product)

image
id, filename

[benanamen]

[member=71845]JimL[/member] ,

This is not right

product
id, name, price, description

Price would not go in the product table. What if you had a small, medium and large size each with its own price? This structure would require you to repeat products for each option which violates DB Normalization.

First of all it's all redundant when you add in the table (product.productname)

I wouldn’t necessarily agree on this. Say you also have services. then you would have

service.name
product.name

When you get into code, you will just have name which will be unclear where “name” is coming from. Especially if both are used in the same page.

I would do
service_name
product_name

Then it is clear and no doubt what you are dealing with.

This gets even worse if you also have parts along with services and products
parts.name

An Automobile business would easily fit all these.

[JimL]

[member=71845]JimL[/member] ,

This is not right

product
id, name, price, description

Price would not go in the product table. What if you had a small, medium and large size each with its own price? This structure would require you to repeat products for each option which violates DB Normalization.

I agree, I just had to draw the line somewhere before it got ridiculous and having a separate price table (for sizes, for periods of time, whatever) just got way out of scope

I wouldn’t necessarily agree on this. Say you also have services. then you would have

service.name
product.name

When you get into code, you will just have name which will be unclear where “name” is coming from. Especially if both are used in the same page. Then it is clear and no doubt what you are dealing with.

I would do
service_name
product_name

I don’t agree, at all. In the code you would have $product->name and $service->name, or similar. There is no valid reason to do tablename.tablename_context, it’s just silly

[benanamen]

Ok in OOP, but what about procedural?

[JimL]

Ok in OOP, but what about procedural?

It would be the same… ie $product[‘name’] and $service[‘name’]

guess it all boils down to how dry you want to be

[benanamen]

@JimL

It would be the same… ie $product[‘name’] and $service[‘name’]

guess it all boils down to how dry you want to be

Lets start at the beginning, the query

SELECT
product.id,
product.name,
service.id,
service.name,
part.id,
part.name
FROM
product ,
service ,
part

See the attachment. Do you see a problem here?

---

[JimL]

See the attachment. Do you see a problem here?

Yes, but in queries where you select duplicate columns you use SELECT AS to separate them. Some potential situation where you might select duplicate columns shouldn’t decide how you name your entity fields. Also we always advocate how you should do separation of concerns, so a product shouldn’t have to care if some other object/table/entity may also hold that same field name. Luckily I use ORMs like Doctrine2, so when I select products with parts I get a multi dimensional array with products that each hold an array of parts that belong to them. No worries any more as each object is isolated

But this is getting off topic for this thread, I suggest you create a different thread if you want to discuss this form of naming convention. I have serious doubt I’ll change my mind about product.product_id, product.product_name, product.product_description etc though.