I've ideas, some which i have gathered with time, however i actually want to understand what makes things go easily for you personally when modeling database:

  1. Table title matches Primary Key title and outline key
  2. Schemas are by functional area
  3. Avoid composite primary secrets where possible (use unique constraints)
  4. Camel Situation table names and area names
  5. Don't prefix tables with tbl_, or procs with SP_ (no hungarian notation)
  6. OLTP databases ought to be atleast in BCNF / 4NF
  • Title similarly targetted saved procs with similar prefix, for example if you have 3 saved methods for Person. This way everything for body's arranged in one location and you will locate them easily without needing to examine all of your procs to locate them.
    • PersonUpdate
    • PersonDelete
    • PersonCreate
  • Do similar things for tables if you have categories of tables with related data. For example:
    • InvoiceHeaders
    • InvoiceLines
    • InvoiceLineDetails
  • If you possess the use of schemas in your database, rely on them. It's much better to determine:
    • Invoice.Header
    • Invoice.Line.Products
    • Invoice.Line.Item.Particulars
    • Person.Update
    • Person.Remove
    • Person.Create
  • Avoid using triggers unless of course there is no other reasonable approach to accomplish this goal.
  • Give area names a significant prefix to help you tell what table they are available from without someone requiring to describe. This way if you notice a area title recommended, it is simple to tell which table it's from.
  • Use consistent data types for fields that contains similar data, i.e. don't store telephone number as number in a single table and varchar in another. Actually, don't store it as being number, basically run into an adverse telephone number I'm going to be mad.
  • Avoid using spaces or any other obscure figures in table/area names. They must be entirely alphanumeric - or maybe I'd my druthers, entirely alphabetic except for the underscore. I am presently focusing on an inherited system where table and area names contain spaces, question marks and exclamation marks. Makes me wish to get rid of the designer every day!
  • Avoid using syntax key phrases as object names it'll cause head aches attempting to retrieve data from their store. I personally don't like needing to wrap object names as [index] that's two pointless chars I did not have to type damn you!

One factor I've not seen pointed out yet:

Never use database key phrases as object names. You don't want to need to qualify them each time they are being used

Should you misspell something whenever you create it, repair it the moment you see it. Don't spend years needing to keep in mind that within this table UserName is actually Usernmae. It's a great deal simpler to repair when there is not much code written against it.

Never use implied joins (the comma syntax), always specify the joins.