October 28, 2015 at 11:02 pm
Comments posted to this topic are about the item The ABCs of Database Creation
October 29, 2015 at 2:39 am
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
October 29, 2015 at 3:37 am
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?
October 29, 2015 at 5:24 am
Overall, this is a great back-to-basics article. Thank you!
Minor quibble: Your article says tinyint goes up to 251. It's 255.
October 29, 2015 at 5:56 am
andrew_dale (10/29/2015)
you sayNOTE: 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.
October 29, 2015 at 5:58 am
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).
October 29, 2015 at 6:01 am
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.
October 29, 2015 at 6:14 am
@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.
October 29, 2015 at 6:32 am
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.
October 29, 2015 at 6:41 am
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).
October 29, 2015 at 6:54 am
Good sound basic walk-through of database creation, with a bit of humor.
October 29, 2015 at 10:50 am
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.
October 29, 2015 at 11:22 am
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]
October 29, 2015 at 12:06 pm
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
October 29, 2015 at 4:22 pm
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 23 total)
You must be logged in to reply to this topic. Login to reply