|
By GuyB - Thu 7 Jan 2016
|
Is there a document detailing the design of the database?
Basically I want to create a custom front end app to do certain admin features to my products that are specific to my needs (using MS Access wth an ODBC connection).
To do this safely I need to understand the design of the database.
For example, in examining the tables, I see in VKatrisProductsVersions that I could have 6 entries for a particular product however the entries appear identical in every way.
eg:
P_ID P_OrderVersionsBy P_VersionsSortDirection P_VersionDisplayType P_Type T_Taxrate CAT_ID LANG_ID P_Live CAT_Live V_Live V_ID V_Name V_Desc V_CodeNumber V_Price V_Tax V_Weight V_DeliveryTime V_Quantity V_QuantityWarnLevel V_DownLoadInfo V_DownloadType V_RRP V_OrderByValue V_Type V_CustomerGroupID P_Name P_Desc P_StrapLine P_PageTitle P_Featured P_SupplierID P_CustomerGroupID P_Reviews P_AverageRating P_DateCreated V_CustomizationType V_CustomizationDesc V_CustomizationCost T_TaxRate2 CAT_CustomerGroupID
58 d - d m 0 28 1 -1 -1 -1 96 PRODUCTNAME SKU_NAME 30 1 1.2 5 0 0 n 40 1 v Dandelion Pear txt 0 y 26/08/2015 3:29:00 PM n 0
58 d - d m 0 28 1 -1 -1 -1 96 PRODUCTNAME SKU_NAME 30 1 1.2 5 0 0 n 40 1 v Dandelion Pear txt 0 y 26/08/2015 3:29:00 PM n 0
58 d - d m 0 28 1 -1 -1 -1 96 PRODUCTNAME SKU_NAME 30 1 1.2 5 0 0 n 40 1 v Dandelion Pear txt 0 y 26/08/2015 3:29:00 PM n 0
58 d - d m 0 28 1 -1 -1 -1 96 PRODUCTNAME SKU_NAME 30 1 1.2 5 0 0 n 40 1 v Dandelion Pear txt 0 y 26/08/2015 3:29:00 PM n 0
58 d - d m 0 28 1 -1 -1 -1 96 PRODUCTNAME SKU_NAME 30 1 1.2 5 0 0 n 40 1 v Dandelion Pear txt 0 y 26/08/2015 3:29:00 PM n 0
58 d - d m 0 28 1 -1 -1 -1 96 PRODUCTNAME SKU_NAME 30 1 1.2 5 0 0 n 40 1 v Dandelion Pear txt 0 y 26/08/2015 3:29:00 PM n 0
Does anyone have any information on where to go to learn more about the DB design (eg a doc explaining what each table is for and how it is used). Someone designed it right? I don't want to break my DB.
Cheers
Guy
|
|
By Tiggywiggler - Fri 8 Jan 2016
|
Guy,
There is not a document that explains the entire database but most of it is named in simple terms that can be understood by reading through it.
In the case of what you have displayed here you are not showing a table, you are showing a view. It may help you to look at the view construct, understand how it is joining fields together and then go and look at the tables to understand what is going on.
I am surprised to see these rows looking exactly the same, I would not expect this, but maybe your actual table data can explain how this has happened. I would expect to see multiple rows with the same data except for the CAT_ID, but your data shows the same CAT_ID for multiple rows.
Without having you actual DB infront of me it is quite difficult to understand what you are experiencing.
|
|
By GuyB - Tue 12 Jan 2016
|
Actually that is a direct copy and paste of table entries in a table datasheet view through Access, no filtering or anything, all columns displayed. It just a regular linked table through a ODBC connection to the database and I opened the table.
And yet I have multiple lines of the same data ??
So I am just trying to work out what's going on.
|
|
By Tiggywiggler - Tue 12 Jan 2016
|
Well the view takes its data from a few table joins, so it is possible that there is a linked table that has multiple outer joins as the result of some data in that table which is different, but that data is not shown in the view.
Here is the links:
FROM dbo.tblKartrisCategories INNER JOIN
dbo.vKartrisTypeProducts INNER JOIN
dbo.tblKartrisProductCategoryLink ON dbo.vKartrisTypeProducts.P_ID = dbo.tblKartrisProductCategoryLink.PCAT_ProductID ON
dbo.tblKartrisCategories.CAT_ID = dbo.tblKartrisProductCategoryLink.PCAT_CategoryID INNER JOIN
dbo.vKartrisTypeVersions ON dbo.vKartrisTypeProducts.P_ID = dbo.vKartrisTypeVersions.V_ProductID AND
dbo.vKartrisTypeProducts.LANG_ID = dbo.vKartrisTypeVersions.LANG_ID LEFT OUTER JOIN
dbo.tblKartrisTaxRates ON dbo.vKartrisTypeVersions.V_Tax2 = dbo.tblKartrisTaxRates.T_ID LEFT OUTER JOIN
dbo.tblKartrisTaxRates AS tblKartrisTaxRates_1 ON dbo.vKartrisTypeVersions.V_Tax = tblKartrisTaxRates_1.T_ID
You could try running a SELECT * against it and then seeing where the different rows are. i.e.
SELECT *
FROM dbo.tblKartrisCategories INNER JOIN
dbo.vKartrisTypeProducts INNER JOIN
dbo.tblKartrisProductCategoryLink ON dbo.vKartrisTypeProducts.P_ID = dbo.tblKartrisProductCategoryLink.PCAT_ProductID ON
dbo.tblKartrisCategories.CAT_ID = dbo.tblKartrisProductCategoryLink.PCAT_CategoryID INNER JOIN
dbo.vKartrisTypeVersions ON dbo.vKartrisTypeProducts.P_ID = dbo.vKartrisTypeVersions.V_ProductID AND
dbo.vKartrisTypeProducts.LANG_ID = dbo.vKartrisTypeVersions.LANG_ID LEFT OUTER JOIN
dbo.tblKartrisTaxRates ON dbo.vKartrisTypeVersions.V_Tax2 = dbo.tblKartrisTaxRates.T_ID LEFT OUTER JOIN
dbo.tblKartrisTaxRates AS tblKartrisTaxRates_1 ON dbo.vKartrisTypeVersions.V_Tax = tblKartrisTaxRates_1.T_ID
|
|
By GuyB - Wed 13 Jan 2016
|
Ok, so this is clearly a case of me knowing enough to be dangerous 
I had a closer look at the tables in SQL Manager and couldn't see the table I was puzzled about, and from your discussion of 'views' I looked further down and saw the list of Views, in which the 'table' was in, which is of course, a View.
I've worked a lot with SQL over the years but more supporting the server installs and back end of it rather than the actual design of the database itself, with most of my database work staying small scale and just using tables so I've never actually needed to use Views, or even stored procedures etc for that matter.
Thank you for your help, I shall continue to tread cautiously in my journey of the database 
Guy
|
|
By Paul - Thu 14 Jan 2016
|
|
The views makes it much easier not just for us but also for users who are writing custom exports for example. It means you can do a select for products, and get out product names and description which are actually stored in the language elements table (this structure allows us to support multiple languages without having a ton of extra fields in the products table that generally will be left empty and will mean a fixed limit on number of languages).
|