Good sources for DB design questions

Databases
1

Dear All,

What are some good sources for learning how to design effective relational databases?

Is here an OK place to ask about basic DB design?

What if I provide pictures with arrows and circles and notes on the back?

Basically, I feel like this kid:

FS2
FS21350×1296 278 KB

Thanks for any and all input.

Regards,

Tom

The design so far…

DB Diagram
DB Diagram825×970 230 KB

2

I have little to contribute on teh DB design but I like the content I am seeing! Ill be anxious to see the final result Thomas, I am a fly fisherman too.

3

Never start any DB design without knowing what you are going to do.
Write a little about the objective.

The design does not seem complete.

4

Back when I was starting out I found “Database Design for mere mortals” good. The book still is in print.

Your database design is problematic. At least half a dozen tables are almost identical.

1 Like
5

Maybe one of the first steps should be reading about “Database normalisation”:
Intro at Wikipedia
and: Database Normalization: A Step-By-Step-Guide With Examples

3 Likes
6

“Database Design for Mere Mortals” is a good recommendation as are the Wiki page(s) on Database Normalization. Those should get you started.
General rule of thumb: each table should be a concept. For example:
Customer
Order
Product
etc.
For repeating elements, like addresses, those become either child tables or intersection tables.
If you are not validating addresses and making sure that they are unique then you likely should make it a child table:
Customer Address

  • Customer Address Id (Primary Key)
  • Address Type Code (Foreign Key from Address Type table ~ you would have things like “Mailing”, Shipping", “Seasonal”, etc.)
  • Customer Id (Foreign Key from Customer table)
  • Address Line 1 Text
  • Address Line 2 Text
  • City Name
  • Zip Code
  • State Code (Foreign Key from State Province table ~ you might be able to get away with calling it just the State table, but you need to check the database platform to make sure it is not reserved key word. A more obvious example of a database reserved key word would be “table”. You never want to name a table “table” as it creates all kinds of issues and funky SQL you just don’t want to have to deal with.)
  • Start Date
  • End Date (people move more than you might think or realize.)
7

Thank you all for your help.

I didn’t mean for anyone to take the ER diagram I posted as something I was actually going to build. That was from a stream of consciousness kind of thing. Sorry.

Again, thanks tons for leads. I have ordered some books!

Tom

1 Like