Newbie needs help setting up database

[zibbler]

Hello,

I'm trying to design a very simple database and am having a great deal of trouble with it. This is my first attempt at making a database. Essentially, right now at work we are tracking customer orders using a spreadsheet. This is very cumbersome and inefficient. I told my boss that a database would be a much better idea and that I would try to put one together.

What I need to do is be able to enter the customers first & last names, the date ordered, date shipped, item, shipping method, and a field for comments. Seems like it should be simple and straight forward, but I'm having a great deal of trouble with it.

I thought 2 tables would be fine. One for customers names and another for the order info. The problem I'm having is trying to create a relationship between the two, so that after the customer is in the database, any orders can be written to the orders table without duplicating the customers names. I thought Customer ID would be a good key to use in each table to relate them, but if I try to enter more than one order per customer, I get an error saying I'm duplicating the key. What am I dong wrong. This should be so simple and has become very frustrating. Any help you could give would be much appreciated. Remember, I'm a newbie (never did this before), so please use plain speak.

Thanks in advance.

 

[JeremyNYC]

Zibbler,

You're headed in the right direction, though it will end up being a bit more complex than you're thinking right now. But there are tons of resources to make it easier for you to get from here to there.

The first one I would check out is Paul Litwin's article Fundamentals of Relational Database Design. I recommend it so often that I've got a copy on my website.

The next one I would check out is some sample databases. The one that comes with Access, Northwind, is actually an order tracking system. It's not a great database, but certainly has enough good in it that it's worth checking out. There are sure to be many purchasing systems on the web that you could download to investigate.

Next, some specifics about your setup. When you have a one-to-many relationship between two tables, you want the primary key of the one side of the relationship (CustomerID, in this case) to be included in the table on the many side, but it can't be the primary key there, and it can't be indexed with no duplicates.

Also, you're going to want to start with at least four tables, though you'll end up with several more. The tables would be tblCustomer, tblItem, tblOrder, and tblOrderDetail. tblItem will list all of the things you sell. tblOrder will list such things as tax, delivery instructions, date of the order, CustomerID (and will have an autonumber OrderID field as the PK). tblOrderDetail will have the OrderID as a number field, the ItemID as a number field, the Cost as a currency (because your prices are going to change over time, so you can't rely on the cost in tblItem), the quantity.

But read that article, maybe a couple of times, and you'll see how all of this stuff makes sense.

And if you have more questions, definitely get back to us.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[zibbler]

Wow! Thanks for the info. I see what I was doing wrong now. I was trying to use the same key in each table. It's a lot more involved than I realized. Where can I get a copy of that article you mentioned?

 

[JeremyNYC]

Glad I could be helpful.

The article is on my webiste, it the Developers' section.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.