ERD Help

Question

ERD Help

bickling77

SSC Veteran

Points: 209
More actions
November 5, 2010 at 12:07 pm

#228610

I was just wondering if someone could take a glance at an ERD i have beeen working on to make sure that I am headed in the right direction. I am new to database design and am not sure if my ERD is error free. Thanks in advance for looking at my post.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

1. Tables are usually plural, ie. Sales, Products, Customers, Employees, etc. (they're not holding just one record, are they?)

2. In your deliveries, you have a SalesRecieptNum field. Is this the same as the Sale.RecieptNum column? (should there be a FK between them? Should it be named just RecieptNum?)

3. Customers: you might want a CustomerID (identity) field there. What happens if your customer changes their name... you don't want to have that particular mess.

Otherwise, it looks okay to me.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Please take a second look at SoldItems.DateSold, I think this is redundant to Sales.SaleDate. If both pieces of information describe the same attribute which is the date/time of the sale, take it out of SoldItems table.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

bickling77 SSC Veteran Points: 209 More actions · Answer 3

Thank you all for the tips on my ERD. I thought I had it pretty much figured out b ut sometimes the foreign keys confuse me a little on where they should go sometimes. I will take the tips that you two have given me and apply them to my ERD. Thanks again for your help.

Here is my revised ERD with if someone could give me some input on this I would greatly appreciate it.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

bickling77 (11/6/2010)
Here is my revised ERD with if someone could give me some input on this I would greatly appreciate it.

mmhhh... still seeing SoldItems.DateSold column. 🙂

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95460 More actions · Answer 5

I would also suggest not having spaces in table/column names. Personally, I prefer where each word starts with a capital letter, ie. SoldItems.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ed B Hall of Fame Points: 3666 More actions · Answer 6

Some questions you may or may not wish to consider.

Do all employees of the same type earn the same amount per hour?

What happens if an employee changes type? gets a promotion?

If you abbreviate a word such a Quantity, its a good idea to be consistent. I see QuantSold and QuanOnHold.

Does the Deliveries table store Customer address or Delivery address? if customer then it seems redundant, if delivery then I would name it accordingly.

thisisjoy SSC Eights! Points: 833 More actions · Answer 7

I see Item Price only on Sold Items. This implies that there is no standard price for an item, that it depends on a sale? Normally I would expect to see Item Price as an attribute of Product, with the potential for an override (if necessary) for a particular sale.

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 8

1. Tables are usually plural, ie. Sales, Products, Customers, Employees, etc. (they're not holding just one record, are they?)

My take, and many others, is that table names are singular. The assumption is that of course they hold lots of records, so we don't need an extra letter to state what is already known.

The date sold at the item level rather than the delivery letter is the most obvious issue that I see, and someone has already pointed that out.

To address the price issue, you could have the price in the product table or possibly have a product price effective date table keyed on product id and date.

bickling77 SSC Veteran Points: 209 More actions · Answer 9

Thank you all for you help with this. I have learned some valuable things with ERD's from the comments eveyone has provided. Again thank you posters for the advice as I am now finished with this and and movinfg on to basic SQL. Im sure I will have some questions about this soon to come.:-)