Database schema modifications

[Sphericus]

Due to our current product system. We have a need for SKU's to be longer than 12 characters.

Would there be an issue with Konakart if we went through and changed database field lengths? Naturally we would only ever make them longer, but with the way KonaKart was developed, would KonaKart have a problem, if it for example received an SKU that was longer than 12 characters?

Thanks.

[john]

Hi Sphericus,

First of all I should tell you that we have introduced a specific SKU field for the next release.   It will appear in the products_quantity table as follows:

CREATE TABLE products_quantity(
   products_id int NOT NULL,
   products_options varchar(128) NOT NULL,
   products_quantity int NOT NULL,
   products_sku varchar(255),
   PRIMARY KEY (products_id, products_options)
);

You'll note that it's a varchar(255).
It will be accessible from the Admin App for modification and searching.

It might be best if you could map your existing product's SKU to this field if you can wait for the next release (it should be out in about 2 or 3 weeks).


But back to your question.   With the exception of some validation failing in some places (I'm thinking of the Admin App) I do not know of any problems that you would encounter if you simply extended database field lengths.   Changing types is more likely to cause problems.

Which field(s) are you thinking of extending?   Perhaps it is a column we might want to extend as a default for KonaKart in future?  It depends which column and whether it's still required given that we are introducing a specific products_sku column in the next release.    Another potential problem is that we do try to remain compatible at the database level with osCommerce; so we would have to take that into account as well.

Regards,
John

[paolo]

Hi Sphericus,

For the case where you don't want different SKUs for different product configurations (i.e. Small yellow shirt, large yellow shirt etc.) we have also introduced an SKU field in the products table (varchar(255)) which you will be able to use. This should be set to zero if you use the mutiple SKUs per product .

-Paolo

[Sphericus]

Which field(s) are you thinking of extending?   Perhaps it is a column we might want to extend as a default for KonaKart in future?  It depends which column and whether it's still required given that we are introducing a specific products_sku column in the next release.    Another potential problem is that we do try to remain compatible at the database level with osCommerce; so we would have to take that into account as well.

We extended Products_model in the Products table. I felt that would be the best place to put the part numbers.
We found for our SKU's the varchar(12) for products_model in the Products table was not long enough, so we extended them to 32.

We are trying to modify your schemas very sparingly. As heavy modification is counter productive to using a specific product I believe.

[Sphericus]

Also just wondering, is there any reason in particular that products_date_available is TIMESTAMP and not just a simple date? Since I cannot really see where it shows the information on the site, also for a e-commerce store, would it make much difference to list a product as available at 9am 13/08/2007 or 13/08/2007?

Just more curious than anything...

[Sphericus]

Hi Sphericus,

For the case where you don't want different SKUs for different product configurations (i.e. Small yellow shirt, large yellow shirt etc.) we have also introduced an SKU field in the products table (varchar(255)) which you will be able to use. This should be set to zero if you use the mutiple SKUs per product .

-Paolo

Well, we were thinking we would use the SKU's we use in our accounting system in KonaKart. In that system every unique object has a dedicated part number.
If you think that this is a bad idea, please let me know. Since we are open to suggestions.

[paolo]

The reason is that in the original OsCommerce MySQL schema it is a datetime which converts to Timestamp in DB2. I suppose that it does give you that extra granularity which most people would never use, but someone may find useful.

[paolo]

I think that that is a very good idea. I only mentioned the SKU at product level because some of our users have products that don't have options and so they only really need one SKU per product rather than many SKUs per product based on color , size etc.

[Sphericus]

Fair enough on the timestamp. Just got a bit curious that is all.

Thank you for the vote of confidence on SKU's