Database Structure.. Ugg ?

Question

Ok, So this may seem silly but I want to make sure I am right.

I have a simple enough database setup:

Table_Customer
  ID  (PK)
  Acc_number
  First
  Last
  etc.

Table_Notes
  ID (PK)
  Note_Type_FK (links to Table_Note_type)
  Note
  Account_FK (Links to Table_Customer)

Table_Note_Type
  ID (PK)
  Note_Type_Name
  Note_Type_Desc

Notesis for all notes relating to that customer and can be for tech support, sales follow up, etc. This table has a FK linked to Note_Type and a FK back to Acc_number in Customer.

My Questions:

Is This set up correctly?
Using SQL, when I add/update a customer and select a note type (eg, Tech Support) and type in a note for that customer, is there anything I should be doing / watching out for when I run the insert statement ?

Its a simple question, but I want to make sure I am doing this correctly.

score 2 · Answer 1 · answered Apr 18 '11 at 14:23

The setup seems correct for a simple CRM sort of software, but without the full system requirements, we can't answer if it is correct or not.

As far as inserting, as long as both the Customer and the Note_Type already exist, everything should be fine. The one thing that trips some people up at first is making sure the cascade rules are set the way they want them on the Foreign Keys. Depending on the requirements, you might want to have all Notes deleted when you delete a Customer (cascade delete), or you might want to require that all Notes are explicitly deleted first, before the Customer is allowed to be deleted (cascade restrict)

score 2 · Answer 2 · edited May 23 '17 at 09:58

Your structure is correct, but you may want to rethink your naming scheme. Although naming schemes can vary widely, it's generally not considered good practice to prefix all your database tables with tbl or Table_. This article authored by Narayana Vyas Kondreddi seems to be one of the most widely accepted guides on SQL Server naming conventions:

http://vyaskn.tripod.com/object_naming.htm

The question of SQL naming conventions has also been answered on this site before, see:

Database Naming Conventions by Microsoft?

score 1 · Answer 3 · answered Apr 18 '11 at 19:27

1

Your approach will work. One thing to mention; you don't need ID in customer table if account number is unique.

Here's a simple ERD for your purpose.

enter image description here

answered Apr 18 '11 at 19:27

mevdiven

1,902
2
17
33

I would caution about using an account number for an ID. Account numbers often have more meaning to them than just identifying an account. They are known to change due to business constraints and have been known to encode other information as well. – cdeszaq Apr 19 '11 at 15:07

Database Structure.. Ugg ?

3 Answers3