Database Design

  • Thread starter Thread starter Need Help
  • Start date Start date
N

Need Help

I need help designing a database. I work for a non-profit company that
offers a myriad of services to the public. Each customer that works with us
is assigned a customer ID #. I called the first table of the database
"General Information" which includes name, address, contact info, etc. In
that table i used the customer ID # as the primary key. So far this is
simple. However each customer does not use every service that we offer and
each of these services do not have a specific ID #. Up to this point i have
created tables for each of the services we offer. Each of the tables for
these individuals services I have not assigned a primary key because the
unique identifier is still the customer ID #(I think i read that this would
be considered a one-to-one relationship). I am having some trouble pulling
queries for specific data. Should this database be designed differently?
 
You'll need to brush up on the concepts of relational database design.
Unlike a spreadsheet, where you'd just add more fields, you need to start
off thinking about the "things" about which you want to store data.

Based on your description, you are interested in: 1. Customers, 2.
Services, and 3. Services-Customers-Signed-Up-For.

That implies three tables.

In a well-normalized relational database, each table will have its own
primary key. ... and that third table will hold a couple of "foreign keys"
([CustomerID], [ServiceID]) that points back to the row in the respective
tables.

That third table helps you resolve the many-to-many relationship it sounds
like you have between customers and services. That is, one customer can
have many services and one service can be signed up for by many customers.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Per Need Help said:
I need help designing a database. I work for a non-profit company that
offers a myriad of services to the public. Each customer that works with us
is assigned a customer ID #. I called the first table of the database
"General Information" which includes name, address, contact info, etc. In
that table i used the customer ID # as the primary key. So far this is
simple. ....
Click to expand...

Devil's Advocate Position: Is there anything that needs tb done
that cannot already be done with an off-the-shelf application?

So far, your description is whispering "Quickbooks" in my ear
 
Back
Top