Help Setting Up Database

[aceoftrades]

I'm new to Access and trying to setup a database for my Kitchen & Bath Remodeling Company while keeping my sanity. Currently I have tables: CustomerT, ProjectT (set up the same as typical Orders table), and many tables with products (ToiletT, VanityT, ShowerDoorT, etc). The reason I set it up this way was because many of the fields will be different for each catagory (ToiletsT will have Shape and Color while ShowerDoor will have Glass and Finish and so on.). I can't figure out a way to relate the 40+ product tables to the CustomerT and ProjectT tables. Should I just have one product table and then have a description table for each product as well? Not sure how to do that if that's the right way...Any help before I pull the rest of my hair out would be much appreciated.

Joe

 

[jdraw]

BUild a data model to get your initial tables structured and related.

You might wish to watch these free videos, that are available to anyone with access to the internet. There are other videos, these are just a sample.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

You can go back and watch these as often as you want.

Good luck with your project.

 

[speakers_86]

It sounds like you are interested in junction tables. If the links above don't help, google 'junction tables'.

 

[Simon_MT]

You should have one Product Table as this will help you in the long run. You can probably cope with a lot of the Products features like dimensions, colour or finish by individual Product codes, some features could be handled using tables whilst descriptors could be used for those features that are outside the Product template.

This will test your sanity!

Simon

 

[gemma-the-husky]

i am sure simon is correct. the last thing you want is lots of different tables for what are just products. the similarities are far more important than the differences. having multiple tables is sure to give you a headache in no time at all.

you should just have a product type field, which will determine whether particular fields are then expected to be populated.

you could get smart and have a mechanism for managing the optional fields, but I would not bother initially.

 

[Simon_MT]

We have this adage the 80% rule. The further you get to 100% to time and effort is required. In other words get the Products file structure as close as you can and use text to some other means to do the rest. see how far you can get with one file and make your own judgement.

I have deployed a system with two stock files, in hindsight it may have been better to have one but there was at the beginning a need to segregate unique works of art and editions. There will always need to this distinction but I could have structurally incorporated into one file.

Simon

 

[pradeepkn]

I want to know how to connect a sites with database. I have tried it many times but all in vein.I am looking for your suggestions.
rafting in rishikesh