Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

An Introduction to Database Design Expand / Collapse
Author
Message
Posted Tuesday, January 18, 2011 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 9:13 PM
Points: 16, Visits: 39
Its an awesome article I came across in recent past for freshers. I have suggested many freshers to see this article for understanding the table design.

Nice example to demonstrate the complete idea of designing table.

Great Work!!!
Post #1049309
Posted Tuesday, January 18, 2011 7:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 6:04 AM
Points: 1,179, Visits: 785
Good Work Paul!


I haven't seen any article that explains the database designing concepts like a story,easy to understand format.

Post #1049324
Posted Tuesday, January 18, 2011 7:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:20 AM
Points: 558, Visits: 1,471
Nice work!! It's often said that to truly understand a concept you should be able to explain it to others in simple terms and you've definitely done that with this article. Simplicity to the point of brilliance. I shall refer to this when explaining database design concepts to customers.
Post #1049341
Posted Tuesday, January 18, 2011 8:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 71, Visits: 481
I believe there are some serious flaws in this article. For example, recepts represent a historical record of a transaction occuring in a moment in time.

If one normalizes the customer as is done in this example, and simply uses a foreign key in each receipt, what happens if a customer changes their address at some later time? or name? (people do get married).

Now, ALL of the historical receipts return the NEW address or customer name, not the address or name of record at the time of the sale.

This can't be correct, and would get most businesses in serious legal trouble. And I personally have seen this type of nonsense in many amateurish attempts at normalization.

This presentation is oversimplified to the point of providing poor instruction I am afraid...



Post #1049369
Posted Tuesday, January 18, 2011 8:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 71, Visits: 481
I should dd that bob's original paper receipts were MORE accurate than the DB design offered. At least the paper receipts would always return accurate info for the customer for the moment in time the sale was completed....


Post #1049374
Posted Tuesday, January 18, 2011 8:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 89, Visits: 464
Ken - I think you missed the "An Introduction". You bring up some points that would be a good follow on article. Why don't you write up an article that follows on to Paul's but adds some more advanced concepts and continues to explain them in a simple way. I'd love to see a part two.


__________________________________________________

Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
Post #1049375
Posted Tuesday, January 18, 2011 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 11, 2014 7:34 AM
Points: 22, Visits: 52
Paul:

Great primer. Showed it to some of my staff and have been able to raise the curiosity level so we can move away from spreadsheets. Once again, great article.

Jim
Post #1049378
Posted Tuesday, January 18, 2011 8:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 71, Visits: 481
re. Inroduction. Well I suppose it depends on what you feel are the "fundamentals" of our work in DB design. Personally, concepts such as natural business keys (the author jumps right into physical issues ala surrogate keys and misses this concept completely), historical record keeping concepts, etc. ARE the fundamentals, from my experience as a trainer.

I just think this is a primer based on someone's mixing up physical with conceptual and logical DB issues- a serious flaw that is the bane of our industry in my opinion...

ken



Post #1049404
Posted Tuesday, January 18, 2011 8:45 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 15, 2014 8:38 AM
Points: 3,665, Visits: 72,432
kenambrose (1/18/2011)
I believe there are some serious flaws in this article. For example, recepts represent a historical record of a transaction occuring in a moment in time.

If one normalizes the customer as is done in this example, and simply uses a foreign key in each receipt, what happens if a customer changes their address at some later time? or name? (people do get married).



This isn't an instruction manual to type 2 SCDs, I'd consider this a typical approach to an OLTP system. The whole purpose of a database like this is that if a customer's data changes that we minimize updates to correct data.

If Paul were writing an article on the basics of designing a data warehouse, then he could cover handling the type 2 SCD here in the ETL process, though I think this would be over Bob's head.

But I've seen too many multi-million dollar ERP databases handle this sort of customer record as "present value" exactly this way to completely discount it.

And if the customer data has changed enough, a new customer record could be created with a foreign key chaining in the same table to point the old record to the new.




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1049418
Posted Tuesday, January 18, 2011 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:41 PM
Points: 71, Visits: 481
re. new customer record. And there is no unique index on the business key. So as a result, an unlimited number of exact duplicate customer records could also be created from the design.

I don't know about you but that is exactly the type of nonsense I see repeatedly in the field when people who do not understand the basic concepts of DB design attempt it.

My statement stands. Bob's orignal paper record keeping is more accurate than the DB design presented...






Post #1049424
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse