The ABCs of Database Creation

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    Comments posted to this topic are about the item The ABCs of Database Creation

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • samot-dwarf

    SSC Eights!

    Points: 984

    Good:

    - hints about the smallest datatypes

    - clustering the table not by the PK but by the ususal query columns (one of the most common mistakes)

    Bad:

    - no normalization - make extra tables for publisher and title / serie

    - I'd never try to drop a database statement before I creating on - if I want to prevent errors I could use the same but negated IF to create the DB only if missing

    Neutral (feature wish list :-)):

    - I think price, buy date, condition (nearly new or half rotten), remark and maybe bookshelf (extra table again) would be nice to have

    - what's about duplicates

  • andrew_dale

    Ten Centuries

    Points: 1201

    you say

    NOTE: There is a lot of available literature about pages, extents, and the affects they have on database design. Because of this, I won't go into the issue. It suffices to say we want to keep our table width below 1024 bytes when possible.

    But the literature you reference https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx says 8060 bytes per row before it has to start moving stuff off page.

    Where did your 1024 byte width limit come from?

  • cschnaars

    SSC Journeyman

    Points: 80

    Overall, this is a great back-to-basics article. Thank you!

    Minor quibble: Your article says tinyint goes up to 251. It's 255.

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    andrew_dale (10/29/2015)


    you say

    NOTE: There is a lot of available literature about pages, extents, and the affects they have on database design. Because of this, I won't go into the issue. It suffices to say we want to keep our table width below 1024 bytes when possible.

    But the literature you reference https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx says 8060 bytes per row before it has to start moving stuff off page.

    Where did your 1024 byte width limit come from?

    Possibly a typo. My bad.

    EDIT: A bit of dyslexia. I was looking at my resources and saw the 1024 x 8KB = 8192 (8060 really) and my fingers typed the first number instead of the 8060. I'll ask Steve to correct it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    samot-dwarf (10/29/2015)


    Bad:

    - no normalization - make extra tables for publisher and title / serie

    - I'd never try to drop a database statement before I creating on - if I want to prevent errors I could use the same but negated IF to create the DB only if missing

    Neutral (feature wish list :-)):

    - I think price, buy date, condition (nearly new or half rotten), remark and maybe bookshelf (extra table again) would be nice to have

    - what's about duplicates

    Ummm, kind of the point of the article. I wanted to point out that there weren't these things. More articles are coming out to address these issues. (Read the last paragraph of the article).

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    cschnaars (10/29/2015)


    Overall, this is a great back-to-basics article. Thank you!

    Minor quibble: Your article says tinyint goes up to 251. It's 255.

    In my defense, I have fumble fingers. I'll get Steve to correct that.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • wagner crivelini

    SSC Eights!

    Points: 858

    @cschnaars, where do I click to LIKE your comment?

    One thing that really annoys me in this community is that bunch of smart asses taking every opportunity to criticize without considering the real purpose of the articles.

    Are there a couple of typos in this article ? YES

    Do they really matter for the target audience? At this point (learning basics), they obviously don´t compromise the author´s goal.

    @Brandie Tarvin, thanks for sharing.

  • sqlvogel

    SSCrazy Eights

    Points: 9416

    A problem here is that Brandie has set out to describe in this article how NOT to design a database but unfortunately some people will miss the significance of that last paragraph (as I did first time around). People may think that Brandie believes he is giving some sound, good advice. That explains the criticisms already posted here and perhaps there are more to come.

    The past record of featured articles on this site is not a happy one, so I'm not at all surprised if people jump to the wrong conclusions and think that this is just another article confirming their low expectations. For that reason I think it would have been more prudent to structure the article differently, i.e. say more about good practice instead of giving an example of poor practice.

  • William Vach

    SSCarpal Tunnel

    Points: 4827

    Overall, this is a good article. One thing that I would like to see added is to set the recovery model to simple:

    ALTER DATABASE [ComicBooks] SET RECOVERY SIMPLE

    GO

    The default for SQL Server is Full recovery. If you don't do transaction log backups, then the tran log will keep growing. For a personal use database, probably not much of an issue, but for a small database for business use, this could turn into a big problem at a later date (I've seen this happen).

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good sound basic walk-through of database creation, with a bit of humor.

  • csj

    Old Hand

    Points: 376

    It could turn into a big problem at a later date, if a production DB's recovery model is set to SIMPLE. Simple for test and Full for production. Are you always able to restore data without the log. I do not think so. It will be easier to learn how to back up both the data and log.

  • Wayne West

    SSC-Insane

    Points: 22586

    William Vach (10/29/2015)


    Overall, this is a good article. One thing that I would like to see added is to set the recovery model to simple:

    ALTER DATABASE [ComicBooks] SET RECOVERY SIMPLE

    GO

    ...

    Also be sure to turn on autoclose and autoshrink. The last job that I walked in to had those set for almost all of the databases, so clearly it was a best practice, at least according to some. :hehe:

    I dug out my comics database from '85-95 in two DBFs, unfortunately I couldn't open my Access version as it was from 1996 and my install thinks it's too old. 4,800 issues between the two. I found 18 characters were enough for publisher and 41 for title, but I wasn't dealing with titles stopping and restarting. I also had fields for flagging whether a particular title was a mini-series or dead and what box number the comic was in.

    I don't buy single issues anymore, but I buy occasional graphic novel collections and was able to get Matt Wagner to sign the two Mage omnibus editions at the Denver ComicCon a few years ago, I also have a signed Darknight GN which is kinda cool.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Eric M Russell

    SSC Guru

    Points: 125055

    Maybe I'm missing something, but the table appears to lack a unique index on the natural key, which would allow duplicates. This would normally be a requirement for any table, but since this table contains a collection of things (comics), I guess it's possible to have duplicates in reality.

    Also, at least on a transactional (or fact) table, I want the rows clustered in the order they are inserted. This can be the actual date of insertion, which is something you need anyhow, and it doesn't necessarily need to be unique. Maybe the date that a comic is added to a personal collection is not an important, but in most of line of business applications it is. For example a car dealership would want to know the date that a car was added to inventory.

    CREATE TABLE dbo.Comic

    (

    AcquisitionDate DATE NOT NULL DEFAULT (GETDATE()),

    PublisherID SMALLINT NOT NULL,

    TitleID INT NOT NULL,

    IssueNo SMALLINT NOT NULL,

    CONSTRAINT pk_Comic PRIMARY KEY NONCLUSTERED ( PublisherID, TitleID, IssueNo )

    );

    CREATE CLUSTERED INDEX CIDX_Comic_AcquisitionDate ON dbo.Comic (AcquisitionDate);

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • mwpowellhtx

    Old Hand

    Points: 356

    When you open up your article with "Creating <anything> can be scary..." you've lost half your audience. Speak for yourself. I personally count it one among many privileges/duties I would typically carry out on the job. Depending on the scale, not to be undertaken without a serious plan for growth, recovery, etc, but not something I would characterize as "scary".

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply