• Use Int Identities where you can. Make them a primary key, and build a proper relational database with enforced foreign keys and then you wont have data integrity issues.

    If your entity has an attribute that is unique: create a Natural Key.

    Using ISBN as the key wouldn't be a good idea if its repeated multiple times through a database (storage cost reasons). Also, SQL Server is able to sort through int columns extremely quickly to find the values it needs, whereas it will take longer for SQL Server to do sorting of ISBN columns. So a string would be more costly and less effecient.

    As far as putting AuthorID into Edition, its redundant: UNLESS your BookID Foreign Key allows NULLS. If nulls are not allowed, then there would always be a BookID: then can always find editions by an author by joining book.

    Try to think of an even bigger chain. If you were to follow that you should have all ID's in a heirarchy and it was a book store, would you store a customers order for a book with a link back to the order, customer, address, store, salesperson, manager, author, edition, publisher, distributor, etc in the OrderItem table? Not likely. You would just store the book (and possibly the edition) in the OrderItem, and the rest you would join through other entities when you needed.

     


    Julian Kuiters
    juliankuiters.id.au