Ok, Which means this may appear silly but I wish to make certain I'm right.
I've got a not so difficult 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
Notes
is perfect for all notes relevant to that particular customer and may be for technical support, sales follow-up, etc. This table includes a FK associated with Note_Type
along with a FK to Acc_number
in Customer
.
My Questions:
- Is setup properly?
- Using SQL, after i add/update a person and choose an email type (eg, Technical Support) and kind inside a note for your customer, can there be anything I ought to do / looking out when ever I run the place statement ?
Its an easy question, but I wish to make certain I'm carrying this out properly.
The setup appears correct for any simple CRM kind of software, but with no full system needs, we can not answer if it's correct or otherwise.
So far as placing, as lengthy as both Customer
and also the Note_Type
already exist, everything ought to be fine. The main one factor that outings many people up in the beginning is ensuring the cascade rules are positioned how they would like them around the Foreign Secrets. With respect to the needs, you might like to have Notes
erased whenever you remove a Customer
(cascade remove), or you might like to require that Notes
are clearly erased first, before the Customer
is permitted to become erased (cascade restrict)
Your structure is correct, but you might want to re-think your naming plan. Although naming schemes can differ broadly, it's generally not considered sound practice to prefix all of your database tables with tbl
or Table_
. This short article written by Narayana Vyas Kondreddi appears to become probably the most broadly recognized guides on SQL Server naming conventions:
http://vyaskn.tripod.com/object_naming.htm
The question of SQL naming conventions has additionally been clarified on this website before, see:
Your approach works. One factor to say you do not need ID in customer table if account number is exclusive.
Here is a simple ERD for the purpose.