SQL Backend Redesign - Possible Ver 5

I do appreciate the effort that folks must have put in over the years to create nopCommerce and I do not want to offend anyone but I have to question the logic used in the database design. Whilst I’m migrating data from another source, I’ve spent a lot of time delving around the SQL backend and regularly find myself questioning why things have been designed as they are.

I’m old school and tend to refer to a database table as a tuple and the fields therein as attributes but to avoid confusion I’ll try to call a tuple a table and an attribute a field but if I slip up you should grasp what I mean.

Look at [Product], it has 81 fields; most of which are redundant for most products. There fields should be in separate tables with related values where appropriate. In short this does not conform to the basics of normalisation.

Then I come to Product Attributes, Colour, Size and so on. Generating the Product Attribute Collections should save time when relating to Products but if I have a range of colours in a collection and the Product only comes in a few some, records are generated for all colours that must then be deleted. It would be wiser to prepare the array of potential colours, refine the list and then commit.

Now I come to how these attributes are stored in the database. The Collections are stored as [ProductAttribute], the values as [PredefinedProductAttributeValue] which contains the likes of [PriceAdjustment] and [WeightAdjustment] which will be different for every Product and so is quite irrelevant. Even if it was relevant the adjustment would not apply in every instance and so these should be separated into another related table. Then of course you find they are repeated in the [ProductAttributeValue] table to give that unique adjustment per entry.

As you may have gathered by now I’m vey strongly of the opinion that the database has evolved rather than being designed and now contains many spurious attributes and areas of duplicate data. I know that 4.3 is imminent but I do wonder it others share my views and whether there is an appetite to redesign the database which will of course necessitate total redesign of the solution. Perhaps a direction for Ver 5?

[email protected] wrote:

"Most"?  Which specific fields concern you?  Others would argue that having them on each product provides more flexibility  (without extra joins, which by-the-way would also impact performance).

I disagree on your performance statement. If anything bloating a table adding unnecessary data hampers performance whereas linking tables in queries to return only what is needed shuld if anything speed things up.
Lets have a look at what we have:
[Id]
      ,[Name]
      ,[MetaKeywords]
      ,[MetaTitle]
      ,[Sku]
      ,[ManufacturerPartNumber]
      ,[Gtin]
      ,[RequiredProductIds] - If applicable therefore should be in related table [RequiredProduct]
      ,[AllowedQuantities]
      ,[ProductTypeId]
      ,[ParentGroupedProductId]
      ,[VisibleIndividually]
      ,[ShortDescription]
      ,[FullDescription]
      ,[AdminComment]
      ,[ProductTemplateId]
      ,[VendorId]
      ,[ShowOnHomepage]
      ,[MetaDescription]
      ,[AllowCustomerReviews]
      ,[ApprovedRatingSum]
      ,[NotApprovedRatingSum]
      ,[ApprovedTotalReviews]
      ,[NotApprovedTotalReviews]
      ,[SubjectToAcl]
      ,[LimitedToStores]
      ,[IsGiftCard] - if applicable so into related table [GiftCard]
      ,[GiftCardTypeId]  - if applicable, so, [GiftCard]
      ,[OverriddenGiftCardAmount]  - if applicable, so, [GiftCard]
      ,[RequireOtherProducts]  - if applicable so into related table [RequireOtherProducts]
      ,[AutomaticallyAddRequiredProducts] - if applicable, [RequireOtherProducts]
      ,[IsDownload] - if applicable so into related table [Download]
      ,[DownloadId]  - if applicable table [Download]
      ,[UnlimitedDownloads]  - if applicable  table [Download]
      ,[MaxNumberOfDownloads]  - if applicable  table [Download]
      ,[DownloadExpirationDays] - if applicable  table [Download]
      ,[DownloadActivationTypeId] - if applicable  table [Download]
      ,[HasSampleDownload]- if applicable  table [Download]
      ,[SampleDownloadId]- if applicable  table [Download]
      ,[HasUserAgreement] - if applicable  table [UserAgreement]
      ,[UserAgreementText] - if applicable  table [UserAgreement]
      ,[IsRecurring]  - if applicable  table [Subscription]
      ,[RecurringCycleLength] - if applicable  table [Subscription]
      ,[RecurringCyclePeriodId]- if applicable  table [Subscription]
      ,[RecurringTotalCycles]- if applicable  table [Subscription]
      ,[IsRental] - if applicable  table [Rental]
      ,[RentalPriceLength] - if applicable  table [Rental]
      ,[RentalPricePeriodId] - if applicable  table [Rental]
    and so on.