Design Problem

[mr_fish]

Hello all,

I'm in the middle of a college project to design a database for a company while at the same time learning access. I've got a slight design problem which I'm not sure how to overcome. The database is attached as a zip file as this seemed to shave 500k of the size of it, any criticisms or suggestions are welcome.

OVERVIEW
The coverage table is where users will log general media coverage and the product that relates to that coverage as well as other useful info, the two linked are MEDIA and PRODUCTS. If the media wish to review a sample of a product a table called SAMPLE_TRANSACTION is available to enter the details as and when required, this is one to one table for efficiency as there my be a lot of general news coverage but little review coverage that actually requires samples to be sent.

My problem is that the product name from the PRODUCT table needs to be entered in the COVERAGE table, yet each product could have several samples, each one needs a separate record to track where it goes, this led me to design a SAMPLE table to store details of each sample related to a PRODUCT.

I'm not sure where to link this SAMPLE table, my idea was to link it to the PRODUCTS table as one product can have many samples, yet I need to be able to link individual sample info from the SAMPLE table into the SAMPLE_TRANSACTION table.

To summarize I need to enter the product foreign key into the coverage table but I also need to be able to enter the individual sample related to the product into the SAMPLE_TRANSACTION table - How should I link these tables?

Thanks to any and all who reply.
View attachment COVERAGE_DB_V1.08.zip

 

[Mile-O]

If the media wish to review a sample of a product a table called SAMPLE_TRANSACTION is available to enter the details as and when required, this is one to one table for efficiency as there my be a lot of general news coverage but little review coverage that actually requires samples to be sent.

Since one-to-one tables are very rare I would say that's your first design mistake.

My problem is that the product name from the PRODUCT table needs to be entered in the COVERAGE table, yet each product could have several samples, each one needs a separate record to track where it goes, this led me to design a SAMPLE table to store details of each sample related to a PRODUCT.

Tell me, what do you know of database normalisation? I wouldn't have a product's name in more than one table; I'd use the relevant primary key as a foreign key in other linked tables.

 

[mr_fish]

I read up on design before starting, although one to one tables are rare, in this case as a sample transaction record is not created every time coverage is obtained and only when a request comes to review a product, I thought it best for efficiency to link it that way (any other suggestions welcomed
)

I also read up on normalization, though it does get a bit hairy past the first couple of normal forms. The idea is that product be entered in the coverage table with a foreign key linked to the product table (where a clients range of products can be entered), at this point product is fine as it refers to the product as a single entity.

My problem comes when a product is requested by the media and a record is entered into the Sample_Transaction one to one table, now I've got a link to the product via coverage table, but I have no way of logging the many individual product samples that my be sent out, that's the problem I'm tryting to overcome.

 

[ScottGem]

I took a look at your database and there are some normalizations issues. You should have a separate table for Addresses and then link to that table from your Transactions, Media and Client tables.

Alos you are using prefixes for fields that have no meaning. I generally don't use prefixes for field names, but if you are going to do so, use ones that describe the field type.

As to the rest, I'm not sure I understand what coverage means. What is the difference between Product and Sample?

 

[mr_fish]

Thanks for your feedback.

The separate address table is an interesting idea, but because the Media information will be imported from another database this may be difficult to implement. The Transaction table has address fields to snapshot which address was used for each transaction, this will give the user the ability to trace if a different address is used to that in Media
(I'll also design the form to auto enter command button if the address isn't different).

The difference between PRODUCT and SAMPLE is that product stores ranges of products (each entry will be a product e.g. Sony Vaio C1000 Laptop) while sample will store details of each physical sample of the product available (e.g. sample 1, sample 2, sample 3 of Sony Vaio C1000 Laptop)

Finally the prefixes were used because I think I misunderstood my tutor's comments on naming conventions and easy referencing in databases. I thought that referencing which table each field was in would be useful for coding in VBA. Not sure what conventions others use, but if you know of any useful ones or links to them let me know.

 

[ScottGem]

Just do a search on naming conventions here and you should find some. Like I said I don't use them on fields. But I use them on other objects. I use tbl for regular tables, tlu for lookup tables and tjx for junction tables. I use frm for forms, qry for queries and rpt for reports. On forms and reports I use txt for text boxews, cbo for combos, lst for lists, chk for check boxes, opt for option groups, etc. It doesn't matter what conventon you use as long as you are consistent.

Getting back to your table design. I see now what Products ans samples are. In that case, Samples is incorrect. Using your example You have Vaio laptops and you have four of them. There would then be 4 records in Samples. All those Units Yes/No fields Should be part of the transaction record not the Sample record. Nor would you have Sample_Transaction_ID in there. You would have Sample_ID as a FK in the Transactions table.