DB Structure Help (1 Viewer)

[add1989]

I was wondering if anybody would be kind enough/have the time to help me with a database I'm currently designing.

The details, including an attachment can be found here:

http://www.access-programmers.co.uk/forums/showthread.php?t=209826

I am trying to achieve this:

Each supplier (DB_Lief) has many unique products which each have a barcode (LiefArtNr) and a Reference Number (RefNr).

Either of these codes can change at any time though, hence an internal ID (DB_Artikel).

I want to be able to record all the details for an article, and then only store changes when there are some. I also want to be able to see, when looking at a product, the previous values of each field and when it was changed.

My current approach to this has been to create a table per 'field', each with same structure allowing the identification of the previous/next value etc. I have however been told that this is the 'completely wrong track', and wish to correct it.

I figure it's worthwhile to move the discussion to this thread to correctly categorise the discussion.

 

[jzwp22]

I could not open the database since I only have Access 2003 here at work, but from what I understand a product can have many codes (one-to-many relationship) so those codes should be in a separate, but related table

tblSuppliers
-pkSupplierID primary key, autonumber
-txtSupplierName

tblArticles
-pkArticleID primary key, autonumber
-fkSupplierID foreign key to tblSuppliers
-txtArticleName

tblCodeTypes (a table to just hold the types of codes Ref and bar, so 2 records in this table)
-pkCodeTypeID primary key, autonumber
-txtCodeType

Now since an article can have many codes over time, we need this table

tblArticleCodes
-pkArticleCodeID primary key, autonumber
-fkArticleID foreign key to tblArticles
-fkCodeTypeID foreign key to tblCodeTypes
-txtCode (the actual code)
-dteEffective (effective date of the code)

Any time any code is changed for an article, a new record would be added to the tblArticleCodes. This will then keep a history of the code changes.

 

[add1989]

Thanks for your suggestions jzwp22.

Attached is a 2003 and translated to English version of my database.

What do you think now?

Also, in your example... How do you tell which is the current ID of each type? In my version I was just checking when the 'NewUID' field was empty.

I should note that one way of explaining my logic, is that I've tried to apply what you've done in your example for the codes... for every field.

 

[jzwp22]

The database is still showing up as a 2007/2010 file format. I have Access 2010 at home so I can look at it tonight.

 

[add1989]

The database is still showing up as a 2007/2010 file format. I have Access 2010 at home so I can look at it tonight.

Can you tell I've had a rough day? Sorry, I zipped the wrong damn file. I'll upload it anyway, incase anyone else finds it useful.

 

[jzwp22]

Your table structure does not make any sense to me. What type of data is contained in each table? You would not have a separate table for each product/article. All articles/products should be in 1 table as I showed in the structure I provided earlier.

 

[add1989]

Your table structure does not make any sense to me. What type of data is contained in each table? You would not have a separate table for each product/article. All articles/products should be in 1 table as I showed in the structure I provided earlier.

The tables aren't per article, they're per article field. For each article I want to know:

• Barcode, Reference Number & PH Code
• Description, Category & Sub-Category
• Material, Weight, Size & Colour
• + other similar info.

There could be 5000 articles per supplier. Currently 12 suppliers. Every so often an official list will be released and this needs to be stored/recalled, so that I can go back to last year and export the price list from month X. I'm also going to be writing some EXE's that can import and export article lists to/from a CSV file.

My way of doing this was to store the UID of each row from each 'field table' in AS_Artikel for each article, therefore not storing new data for the price list - only links. Make sense?

 

[jzwp22]

The tables aren't per article, they're per article field. For each article

That's not the right approach either


All code numbers (Barcode, Reference Number & PH Code) related to an article are Records in tblArticleCodes


If an article has 1 and only 1 description, then you would need a description field in tblArticles

tblArticles
-pkArticleID primary key, autonumber
-fkSupplierID foreign key to tblSuppliers
-txtArticleName
-txtDescription

A table to hold all colors
tblColors
-pkColorID primary key, autonumber
-txtColor

With respect to materials, can an article be made from multiple materials or just 1.

Are the size and weight related to an article? Do you need to deal with dimensions such as length, width, volume etc.?

What do you mean by Category & Sub-Category?


By the way, what are the articles you are dealing with? clothing articles such as shirts, pants etc.?

 

[add1989]

If an article has 1 and only 1 description, then you would need a description field in tblArticles

I want to store a CURRENT description in different languages (DE, EN, etc).

With respect to materials, can an article be made from multiple materials or just 1.

Material is only one item.

Are the size and weight related to an article? Do you need to deal with dimensions such as length, width, volume etc.?

Size and weight of the article (e.g. ring size). No dimensions, just a text field.

What do you mean by Category & Sub-Category?

Category: Jewellery, Sub-Category: Charms

By the way, what are the articles you are dealing with? clothing articles such as shirts, pants etc.?

The articles are generally jewellery and/or accessories.

I want to be able to see current and previous entries for the fields. Text I will have different languages for, prices - different currencies.

 

[jzwp22]

I want to store a CURRENT description in different languages (DE, EN, etc).

I think Access can only handle 1 language for an application, but I may be wrong. It's not something with what I am familiar.

Material is only one item.

I assume that many articles can be made from the same material, so it would be best to have a table that holds all possible materials (as records) and then relate the material to the product via a foreign key.

tblArticles
-pkArticleID primary key, autonumber
-fkSupplierID foreign key to tblSuppliers
-txtArticleName
-txtDescription
-fkMaterialID foreign key to tblMaterials

tblMaterials
-pkMaterialID primary key, autonumber
-txtMaterial

Size and weight of the article (e.g. ring size). No dimensions, just a text field.

Size and weight are dimensions as such since you have more than 1 dimension per article, then they should go in a separate but related table

tblArticleDimensions
-pkArtDimID primary key, autonumber
-fkArticleID foreign key to tblArticles
-fkDimensionTypeID foreign key to tblDimensionTypes
-dimensionvalue

tblDimensionTypes (2 records size and weight)
-pkDimensionTypeID primary key, autonumber
-txtDimensionType

Category: Jewellery, Sub-Category: Charms

It would be best to have one table to hold all categories main & sub and then relate them to each other in another table.

tblCategory
-pkCatID primary key, autonumber
-txtCategoryName

tblCategoryRelate
-pkCatRelateID primary key, autonumber
-fkMCatID foreign key to tblCategory (represents the main category)
-fkSCatID foreign key to tblCategory (represents the sub category related to fkMCatID)

You would then relate the related main/sub categories back to the article

tblArticles
-pkArticleID primary key, autonumber
-fkSupplierID foreign key to tblSuppliers
-txtArticleName
-txtDescription
-fkMaterialID foreign key to tblMaterials
-fkCatRelateID foreign key to tblCategoryRelate

I want to be able to see current and previous entries for the fields.

For every field? Why would things change that much?

I can see prices changing and that you would want a history of that. On the topic of prices, you would store the price in 1 currency and then have another table that holds currency exchange rates and then you would do the calculation when you need it.

Since an article can have many prices:

tblArticlePrices
-pkArticlePriceID primary key, autonumber
-fkArticleID foreign key to tblArticles
-currPrice
-dteEffective (effective date of the particular article price)

tblCurrency (a table to hold all currencies except your reference currency)
-pkCurrencyID primary key, autonumber
-txtCurrencyName

There can be many currency exchange rates over time

tblCurrencyExchangeRates
-pkCurrExchRateID primary key, autonumber
-fkCurrencyID foreign key to tblCurrency
-spRate
-dteEffective

The only thing we did not cover was color. Can articles come in many colors and still be considered the same article or is an article in part defined by its color?

 

[add1989]

Thanks very much for your help so far jzwp22. It is very much appreciated!

Attached is a PNG of what I've got at the moment (from what you've said and some stuff I've thought of)...

For every field? Why would things change that much?

I can see prices changing and that you would want a history of that. On the topic of prices, you would store the price in 1 currency and then have another table that holds currency exchange rates and then you would do the calculation when you need it.

I have to be able to reproduce each released price list. And if anything has changed (such as a category from 'Bead' to 'Charm'), description, etc, then these changes need to be remembered exactly.

With regards to the language/currency... The price needs to be stored exactly, not converted - because it is not always a straight-forward conversion. I just need to be able to find the current price in each currency, for the purchase and retail prices (as well as having a history etc).

Language, I would have thought could be done similar to an invoice... Article = Invoice, Language/Description = InvoiceLine, type of thing. Does that make sense?

Also attached is the QSEE project file, are you familiar with this? If not I could just make it straight into an MDB.

 

[jzwp22]

I have to be able to reproduce each released price list. And if anything has changed (such as a category from 'Bead' to 'Charm'), description, etc, then these changes need to be remembered exactly.

Based on the above it sounds like you need add audit trail capability. Allen Browne describes on such way on his website.

The price needs to be stored exactly, not converted - because it is not always a straight-forward conversion. I just need to be able to find the current price in each currency, for the purchase and retail prices (as well as having a history etc).

Then you will need a different structure, something like this perhaps

tblArticleCurrency
-pkArticleCurrID primary key, autonumber
-fkArticleID foreign key to tblArticles
-fkCurrencyID foreign key to tblCurrency

tblCurrency (a table to hold all currencies)
-pkCurrencyID primary key, autonumber
-txtCurrencyName

tblArticleCurrencyPrice
-pkArtCurrPriceID primary key, autonumber
-fkArticleCurrID foreign key to tblArticleCurrency
-currPrice
-dteEffective

Language, I would have thought could be done similar to an invoice... Article = Invoice, Language/Description = InvoiceLine, type of thing. Does that make sense?

I'm not sure what to tell you about the language issue. How would you use the language information?

I could not open your project file.

 

[add1989]

In your example... can't 'curPrice' and 'effectivedate' simply be added to tblArticlePrice ? I don't see why there are three tables there...

How would you use the language information?

Well each text field would have it's equivalent in a different language... So a Category of 'Jewellery' in language 'EN' would have 'Schmuck' as a "relative" in language 'DE'.

I then want to be able to create a price list simply by specifying 'DE EUR' or 'EN GBP' etc.

I'm going to model what we've got up to now into an MDB and play around with it

 

[jzwp22]

In your example... can't 'curPrice' and 'effectivedate' simply be added to tblArticlePrice ? I don't see why there are three tables there...

No because for each article/currency combination (tblArticleCurrency), you will have many prices which is a one(article/currency)-to-many(price) relationship which warrants the structure I have proposed.

Well each text field would have it's equivalent in a different language... So a Category of 'Jewellery' in language 'EN' would have 'Schmuck' as a "relative" in language 'DE'.

Technically speaking if an article can have many descriptions (whether they are the same language or something else) then that is a one to many relationship. I'm just not sure how Access will handle the language issue. You would have to move the description field into a separate but related table to the tblArticle and specify the language

tblArticleDescriptions
-pkArtDescID primary key, autonumber
-fkArticleID foreign key to tblArticle
-txtDescription
-fkLangID foreign key to tblLanguages

tblLanguages (will hold French, English, German, etc. as records)
-pkLangID primary key, autonumber
-txtLanguageName

 

[add1989]

What do you think? (attachment)

I can't figure out how I would output this into 1 long CSV line.

e.g. 5700302etc,790333,J08238438etc,Charm,Charm,Silver,Blue,Silver Hedgehog Charm,30.00,60.00

I don't know if the audit stuff is what I'm looking for? It seems to be more of a logging tool than being able to have all the data still stored. Unless I've misunderstood it?

 

[jzwp22]

Other then a few relationships, everything seems to be in order. (see attached).

can't figure out how I would output this into 1 long CSV line.

e.g. 5700302etc,790333,J08238438etc,Charm,Charm,Silver, Blue,Silver Hedgehog Charm,30.00,60.00

You will need a query to bring everything together. You might also need a custom function if you want to concatenate all of the descriptions for an article. This site has such a function.

I don't know if the audit stuff is what I'm looking for? It seems to be more of a logging tool than being able to have all the data still stored. Unless I've misunderstood it?

The audit trail would be if you want to track changes to fields. If you want a history, you would approach it much like the pricing structure you have.