someone to review my database

[hatman]

i am an extreme beginner to access. I have compiled a database for customers products and orders. I need some help with some problems I ran into.


one- I want to make sure the database is set up right?
second- if not how to correct the design?
third-- I tried running a query and now my base has changed, how to correct.

can someone have a look and see if it is right? and how to send to you?
thanks
hatman

[Xipooo]

Sure. Zip it up and I'll take a look.

[hatman]

How do I attach the file here? no place to attach

[Xipooo]

Click on the "Go Advanced" button under the bottom right of the "Quick Reply" box. On there you'll see an attachment button.

[hatman]

ok think I did it right

[Xipooo]

I'd say the setup of your tables is a bit wrong.

In general when you are building an Orders/Order Details/Products relationship you want the data structured this way.

Order Table
- ID (autonumbered)
- CustomerID (foreign key of the customer)
- AddressID (foreign key of the address to deliver)
(add any additional keys to point to things like phone numbers, contact names, etc which may be specific to this order)

OrderDetails table
- OrderID (Foreign key of the order this particular line belongs to)
- LineNumber
(The OrderID and LineNumber are combined to make the composite key of the OrderDetails table. You could also add an OrderDetailID field which is auto-numbered instead.)
- ProductID (foreign key of the product ID)
- Quantity

Products Table
- ID (autonumbered)
- ProductName
- Cost
- Price
- VendorID (optional if you get that far)


Notice that the pricing is reserved for the product and is not stored as part of the order or order details. That's because you can calculate this in a query or on a report. If you want to store the finalized costs of a purchase, generally you save that in a separate table of Invoices once an order is completed and the goods have been purchased. This adds flexibility in pricing.

I would suggest making these changes before you move on to creating your queries and forms. You will find that what you need to do in them has changed. For anyone else reading, this is a prime example of why you should design your tables and relationships first before beginning on the queries, forms, and reports.

[orange]

A few comments:

It would be helpful to readers if you provided a 3-5 line overview of What the database is about, Without readers are guessing.

All tables should have a Primary Key defined - it should uniquely identify every record iin the table.
CustomerT does NOT have a PK
CustomerT should be related to OrderT 1 to Many (a customer can have many Orders)
What is the purpose of 2 copies of ProductT??
I doubt that Taxable/nonTaxable goes with Order ---probably Product.
You do NOT need Totals or SubTotals in Tables --calculate then in queries or reports when needed.
OrderDetailsT should record the Quantity(number of units) and Selling Price(per unit) of the Product. [If you don't do this you'll have trouble changing prices]

I would Not use multivalue or attachment fields in any Table ----Personal choice.
Seems you forgot CustomerId PK in CustomerT since you allowed for the FK in OrderT.

Always get your tables designed to meet your requirements before jumping in to Forms and Reports ---Personal Choice and highly recommended.

Good luck with your project.

See this link for tutorial on database design/EntityRelationshipDiagramming.

[Xipooo]

A few comments:

It would be helpful to readers if you provided a 3-5 line overview of What the database is about, Without readers are guessing.

All tables should have a Primary Key defined - it should uniquely identify every record iin the table.
CustomerT does NOT have a PK
CustomerT should be related to OrderT 1 to Many (a customer can have many Orders)
What is the purpose of 2 copies of ProductT??
I doubt that Taxable/nonTaxable goes with Order ---probably Product.
You do NOT need Totals or SubTotals in Tables --calculate then in queries or reports when needed.
OrderDetailsT should record the Quantity(number of units) and Selling Price(per unit) of the Product. [If you don't do this you'll have trouble changing prices]

I would Not use multivalue or attachment fields in any Table ----Personal choice.
Seems you forgot CustomerId PK in CustomerT since you allowed for the FK in OrderT.

Always get your tables designed to meet your requirements before jumping in to Forms and Reports ---Personal Choice and highly recommended.

Good luck with your project.

See this link for tutorial on database design/entity Diagramming.

Hee hee hee... you said entity. I see some OTHER coding platforms have ensnared you.

[hatman]

Xipoo...any chance you could set it up the way it should be and send back???
you kinda lost me on this....
would appreciate if you could.
thanks
hatman

[hatman]

sorry Orange
I am a sales rep and want to track sales I get from my customers.
I did have the tables set with relationships but somehow they got lost when I tried doing a query, the customer_old changed from CustomerT.
this was my first attempt at trying to make a database.
hatman

[Xipooo]

A few things to note.

• This is just a STARTING point. But it should give you a relatively good idea of how to organize your data into a normalized table structure.
• I have not put any of your data into it.
• Cost is meant to be the cost to the company, Price is what you sell it for.
• SortOrder fields help when you're creating lists or comboboxes so that you display the type of Address, Phone Number, or Company Type in a specific order on the users screen.
• Examples of types of Addresses would be Ship To, Office, or Residential
• Examples of Types of Phone Numbers would be Office, Cell, and Fax.
• Examples of Company types would be Customer, Vendor, or Referral.

Feel free to use this as a starting point for any of your databases. It's pretty generic and scalable.

[hatman]

looks different than I had. couple questions
do I need all these different table? can I not just keep phone,phone types all in customers table?
as far as products I only sell the product to customers so do I just keep cost. also can I add my different fields here for all details.
I use product item number as an identifier so do I change auto num in primary to text?
I was hoping to maybe have 4 or 5 tables for this.
thanks again

[Xipooo]

looks different than I had. couple questions
do I need all these different table? can I not just keep phone,phone types all in customers table?
as far as products I only sell the product to customers so do I just keep cost. also can I add my different fields here for all details.
I use product item number as an identifier so do I change auto num in primary to text?
I was hoping to maybe have 4 or 5 tables for this.
thanks again

Yes, you can take out whatever tables you don't need.. just make sure you take out the field that reference those table ID's in the tables you decide to keep. I know it's kind of a bit more complex than what you were going for, I just wanted to put something out there for anyone else who might be interested in the basics of a Customer/Contact/Orders/Items table design. You may also find yourself needing those tables you thought you didn't somewhere down the road. You can look back at this structure at a later time to see how it flows together.

You can change the autonumber fields to text if you want to. Just know that you will have to create a new unique value for it every time you want to add a new entry.

Add whatever fields you need to whatever tables, just make sure it's logical to put it there. Such as, don't put the price for an item in the OrderDetails table if it makes more sense to associate the price with the item.

[hatman]

Xippo
I have attached another zip of the changes I made to my database. I deleted all the tables I did not want.
I took out all query's that created my mess. do I have these tables set up right now?
am I able to run query's on these tables now to see what customers have purchased from me.
I know it is not quite how you recommended to do.
would you recommend any other tables to make? if so which ones should I change?

as far as non taxable and taxable, I used them from my order sheets I get from company with my sales. I do not need to keep track of tax on customers. I used a calculated formula when entering info from sheet to get their totals. hopefully I am making sense on this.

any suggestions let me know.
appreciate the help
hatman

[Xipooo]

Yes Hitman, that looks better. A couple of things to consider, but aren't of too much importance....

In general it is good practice to

• Name your tables with the plural tense of the word
• Prefix the object type instead of appending it
• Name your primary key column just "ID"
• Foreign key columns use the Singular word with "ID" appended

For Example, this is your current setup.



It would be better to rename "CustomerT" something like TCustomers, or tblCustomers. Then change your CustomerID column name to just "ID". Below is a view of the changes.




This is admittedly somewhat nit-picky, I just thought you or anyone who reads this thread in the future, might like to know the industry standard. You can name it whatever you want that makes sense to you.

I would also consider NOT using attachments unless you really feel it is necessary to package them inside your database. An Access Database cannot get any larger than 2 GB in size. Attachments are one of the primary reasons a database will exceed this limitation. Instead, store them in a sub folder of where the database is and use a text column that points to the file. If you are planning on distributing this database as a "Front End" somewhere down the road, then use a folder on the network somewhere and save the full UNC path to that file (that means NO mapped or local drive letters).

Lastly, I would break up your customers table into two tables. One for the company information and another for your contacts. Chances are you will end up knowing multiple people from one company. In your current structure you would have to duplicate information in your CustomerT table for each person in that company.