best practices in making the tables

[icemonster]

just want to read or hear good practices in terms of developing the table part of the sql server, like when creating a directory, do you store addresses in a different table altogether and just assign an FK to the client/employee table or create a table seperately with both client/employee and address as FK for that table?

thank you!

 

[MarkK]

The only 'rule' is never duplicate data, and there are two kinds of duplication...
1) Data: If you have an employee table and a related table of employee tasks, do not store the employee name in the tasks table.
2) Structure: If an employee has many tasks never do Task1, Task2, Task3, ..., TaskN in a single record.
Beyond that the structures are determined by your needs. If an client might have many addresses then you need a client table and an address table. If a client will only ever have one address then that address can be stored in the client table.
Merry Christmas,
Mark

 

[icemonster]

well that sounds about it, i just needed to confirm cause i never had made an sql back end before only msaccess but yeah, it guess it's a good idea to do what you said. thanks!

 

[mdlueck]

cause i never had made an sql back end before

hhhmmm... no matter if it be an SQL back end or a desktop database, I always tend to design the schema pretty much the same. I got my RDBMS "formal" training in the early 90's - Paradox and DB2 training back then.

Thinking back, I took the DBA track of DB2 training. That taught very little of proper DB design, rather emphasized on installation and set up of the complicated beast that DB2 v2 was.

So I suppose most of my DB design knowledge has been learned through books and tutorials.

I think I got more proper DB design training from the Paradox courses, actually.

 

[SQL_Hell]

Normalisation example

http://phlonx.com/resources/nf3/