Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Good Database Design Books?

timothy posted about 4 years ago | from the this-slot-that-tab dept.

Databases 291

OneC0de writes "I am the Director of IT for a small/medium sized marketing company, where I personally write the code that runs our applications. We use a variety of technology at our office, the majority of which rely on MS-SQL and MySQL databases. I am familiar with tables, SQL queries, and have a general understanding of how the SQL databases work. What I'm looking for is a good book, particularly a newer book, to explain general database design techniques, and maybe explain some relational tables. We have some tables that have million of rows, and I'd like to know the best method of designing these tables."

cancel ×

291 comments

A Few Suggestions (5, Interesting)

eldavojohn (898314) | about 4 years ago | (#32845484)

We have some tables that have million of rows, and I'd like to know the best method of designing these tables.

I'm a developer, not a database expert. But it seems that every now and then I have to get my hands dirty with data modeling. "The best method" is probably a really vague concept. If you have serious hardware constraints than the best method changes from an easily maintainable system to something more complex. There's give and take in database design and I guess a million rows is really something that a traditional relational database should be able to handle. So I'd suggest any book that teaches data modeling will suit you here. I happened to learn on Data Modeling Essentials [amazon.com] which was decent but not great. I have heard good things about Len Silverston's growing series that concentrates more on patterns. But really what you're going to want is a book on data modeling or analysis that teaches you the orders of normal form, when to use cross reference tables, etc so you can get a better idea of good modeling standards. At a million rows, you might not find the need to refactor if you read about the new best practices but perhaps you could make a business case to eventually migrate.

Now there are other topics that require entirely separate books because they are such a diverging path from relational databases. It's not common but your database can be based on something other than an object or table [wikipedia.org] . If you consider the internals of Google, perhaps BigTable is the most prolific database implementation out there and while interesting [google.com] , it is sort of a very specific proprietary database implementation. You could take this approach to tailor your company's database to be precisely what you need but this would clearly be overkill in your case. You don't talk about any bottlenecks or impending loads that need to be carefully considered so instead of treading down this path, I suggest you first take a course on MySQL or get the de facto book on whatever database you use [amazon.com] and play around with fine tuning on a test system. A lot of DBs out there allow you to tune them through a configuration file so that your particular needs are met more closely. If you're looking for this sort of continuing education just out of curiosity, pick up a book on database design and start to tinker. But it requires a lot of knowledge and effort to start a database technology from scratch and compete with vanilla out of the box technologies like MySQL and PostgreSQL.

From what information you provide in your question, I'd suggest this book to help you understand database designs more via industry proven patterns [amazon.com] . That assumes you have all the basic database design practices covered.

Re:A Few Suggestions (2)

OneC0de (1851710) | about 4 years ago | (#32845618)

Thank you for the great and informative reply!

Re:A Few Suggestions (4, Informative)

RobertM1968 (951074) | about 4 years ago | (#32845624)

We have some tables that have million of rows, and I'd like to know the best method of designing these tables.

I'm a developer, not a database expert. But it seems that every now and then I have to get my hands dirty with data modeling. "The best method" is probably a really vague concept. If you have serious hardware constraints than the best method changes from an easily maintainable system to something more complex. There's give and take in database design and I guess a million rows is really something that a traditional relational database should be able to handle. So I'd suggest any book that teaches data modeling will suit you here.

eldavojohn makes some excellent points and gives some great suggestions. Keep in mind, like elda suggests, nothing is cut and dry. Configuration, resources, numbers of connections for specific data, etc; all will have an impact (or should) on what you should do and how you should design.

Re:A Few Suggestions (0)

Anonymous Coward | about 4 years ago | (#32845792)

We have some tables that have million of rows, and I'd like to know the best method of designing these tables.

I'm a developer, not a database expert. But it seems that every now and then I have to get my hands dirty with data modeling. "The best method" is probably a really vague concept. If you have serious hardware constraints than the best method changes from an easily maintainable system to something more complex. There's give and take in database design and I guess a million rows is really something that a traditional relational database should be able to handle. So I'd suggest any book that teaches data modeling will suit you here.

eldavojohn makes some excellent points and gives some great suggestions. Keep in mind, like elda suggests, nothing is cut and dry. Configuration, resources, numbers of connections for specific data, etc; all will have an impact (or should) on what you should do and how you should design.

Not to mention taking into account how the tables will be used and populated. If the database will be populated by the user, you can get more efficiency with greater amounts of normalization than you will if the database is being populated from an external source (which I'm doing now with text-based reports that are meant to be read by humans and not computers). You could normalize imported data, but this will cause a larger amount of processing to do lookups on the imported data to find the appropriate foreign keys (or add new rows to the foreign tables to accommodate new data).

The general rule is that you want to have as little repeating data in your tables.

Re:A Few Suggestions (1)

cayenne8 (626475) | about 4 years ago | (#32846572)

Hmm.....my quick reading of the topic for this thread, leads me to believe that this IT person started off database knowledge with [shudder]MS Access. Something that should have been BANNED from usage lest another PHB got ahold of it, designed a database for the team to use with 2 tables...that because the office and company standard until it became so unwieldy, that they turn it over to a DBA/Data Modeller type...and say "Hey, throw this on Oracle to make it better".

And thus begins...the normalization nightmare.

Or...am I the only one that not only had to unravel the mess, learn the company's business rules to find their true data needs, to then face the daunting task of cleaning the fucking data (don't ya love free form text fields complete with hard returns in them?)...and trying to load it into the proper database structure.

Then...they complain they don't understand the SQL and need to join tables in a query...etc.

PLEASE take these "database tools easy enough for the boss to use" out of circulation, can't someone please think of the childer....er....database guys?

Re:A Few Suggestions (0)

Anonymous Coward | about 4 years ago | (#32845684)

Beginning Database Design could be considered a 'lite' version of the above recommendations. Note that all three of these books tend to be heavy on UML and light on SQL statements. I suspect you are looking for a book titled 'Help me normalize/fix this here database' . I have not yet found that book either. That said, Beginning Database Design will give you enough of a taste of Data modeling to start thinking about your situation.

Re:A Few Suggestions (1)

fyngyrz (762201) | about 4 years ago | (#32846158)

'Help me normalize/fix this here database'

Oh, no. No. He should be looking for 'Help me normalize/fix that there database'

Much more erudite presentation, start to finish. Recommended by Dale Earnhardt, so it must be fast, too.

Re:A Few Suggestions (5, Informative)

hguorbray (967940) | about 4 years ago | (#32845708)

http://www.amazon.com/Case-Method-Entity-Relationship-Modelling/dp/0201416964

I used this book at Foothill college in an intro to data management class and it taught me more than any of the dozen oracle classes I took once I got past the terminology of tuples, etc

this one is also well-recommended:
http://www.amazon.com/Database-Systems-Design-Implementation-Management/dp/0760049041

and this one is good for people without dba or architect background:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840/ref=sr_1_1?ie=UTF8&s=books&qid=1278629171&sr=1-1

I would stay away from the vendor specific books as good database design s/b dbms agnostic

-I'm just sayin'

Somewhere, a coder is polishing his resume (1, Insightful)

RobertB-DC (622190) | about 4 years ago | (#32845498)

I am the Director of IT for a small/medium sized marketing company, where I personally write the code that runs our applications.

I'm sure I'm not the only code monkey who shudders at the implications of this statement.

Some of us coders are well-nigh insufferable already, because they think they're Superprogrammer and can leap tall mainframes in a single bounds check. The problem comes when those types find themselves in management -- but won't let go of the programming reins. Now, the IT guy with a new idea doesn't just have to worry about it getting shot down by the hotshot who knows everything -- he has to worry about whether his *career* will get shot down by Mr. Hotshot Coder-Manager.

There's a great chance that the article poster isn't like that. But I'm worried, because Ask Slashdot isn't who he should be asking... he should be asking the coders he manages how to design and/or restructure the database. The fact that he's asking Slashdot tells me that he's not comfortable letting someone else do the work, possibly because he's Superprogrammer and always knows what's best.

But in case that's not the situation -- maybe he's in a company that simply grew faster than its staffing could handle -- I'll answer the question. He wants a good book to explain general database design techniques. My answer: buy a good book on management techniques, because *that* is your job. Let the people you manage come up with a database design, because that is *their* job.

Re:Somewhere, a coder is polishing his resume (3, Insightful)

Albanach (527650) | about 4 years ago | (#32845556)

I'm sure I'm not the only code monkey who shudders at the implications of this statement.

Well, it depends on the size of the company does it not? Perhaps they employ fifteen to twenty staff with an IT department of 2 or 3, mostly focused on hardware and user support. Then it would be much more reasonable for the Director of IT to be a coder who is also taking management responsibility.

You're right that if the company grows, management should be the focus and a decent DBA employed, but until then like many small companies the poster may have to be a jack of all trades. At least they're showing incentive in seeking to master at least one of their areas of responsibility.

Re:Somewhere, a coder is polishing his resume (3, Interesting)

Anrego (830717) | about 4 years ago | (#32845584)

.. either that or he's the only programmer in the company and can thus effectively call himself whatever he wants.

Re:Somewhere, a coder is polishing his resume (1)

sgbett (739519) | about 4 years ago | (#32845844)

In those situations one might do well to still call oneself 'code monkey', I know I do.

Re:Somewhere, a coder is polishing his resume (1)

bsDaemon (87307) | about 4 years ago | (#32846136)

Is that one of those "i think its ok when they say it" things?

Re:Somewhere, a coder is polishing his resume (1)

sgbett (739519) | about 4 years ago | (#32846350)

Don't ask me, I'm just the code monkey! ;)

Re:Somewhere, a coder is polishing his resume (2, Insightful)

m509272 (1286764) | about 4 years ago | (#32845662)

Does and doesn't. Shouldn't be making up titles that don't fit an IT department size of 2 or 3. How about "I run the IT department". That's like me in my one-person company calling myself CEO, COO, CIO, Chairman, etc. It's BS. Someone asking for help should leave out the fake title crap and avoid these type of responses.

Re:Somewhere, a coder is polishing his resume (1, Insightful)

IICV (652597) | about 4 years ago | (#32845738)

Shouldn't be making up titles that don't fit an IT department size of 2 or 3. How about "I run the IT department". That's like me in my one-person company calling myself CEO, COO, CIO, Chairman, etc. It's BS. Someone asking for help should leave out the fake title crap and avoid these type of responses.

Although I agree that if he's asking for help he probably should have gone for the more humble "I'm a one-man IT department" approach, but I just wanted to point out that quite frequently when it's a small company and there isn't much money coming in people get "paid" with lofty titles. "Sure, it may only be a 10 person company, but I'm the Director of IT!" sort of thing. If nothing else, it looks good on your resume when the company folds.

Re:Somewhere, a coder is polishing his resume (2, Interesting)

Albanach (527650) | about 4 years ago | (#32845740)

Does and doesn't. Shouldn't be making up titles that don't fit an IT department size of 2 or 3.

I see no evidence that he made up a title. Typically in business, Director has a meaning - that the holder is on the board responsible for running the company. In a small business they will often be one of the shareholders too.

Titles like CIO would indeed be superfluous in a small company, but Director has a specific meaning and its use could be entirely appropriate.

Re:Somewhere, a coder is polishing his resume (2, Informative)

afidel (530433) | about 4 years ago | (#32846066)

Huh? I'm employed by an S&P 500 and director is the title above manager and below VP. Looking at the definition of IT Director in the first dozen hits on Google seems to match that.

Re:Somewhere, a coder is polishing his resume (2, Informative)

Albanach (527650) | about 4 years ago | (#32846390)

In a small a Director is usually someone who sits on the board.

In a large company like an S&P500 one, a director is usually a management position with responsibility for a specific business area.

The titles are the same but the meaning different. I'm assuming from the size of business the poster described (50 employees) that he is in the former category of Director.

http://en.wikipedia.org/wiki/Corporate_title [wikipedia.org] describes both types of director.

Re:Somewhere, a coder is polishing his resume (1)

DragonWriter (970822) | about 4 years ago | (#32845968)

Shouldn't be making up titles that don't fit an IT department size of 2 or 3.

If the person has similar budgetary independence and authority within the organization, e.g., over cross-cutting concerns as other "Directors" within the organization, the title is appropriate even if the person has no subordinate staff.

Re:Somewhere, a coder is polishing his resume (5, Informative)

OneC0de (1851710) | about 4 years ago | (#32845598)

I'm the article poster. Our company is relatively small, with an IT staff of less than 5, and total company size less than 50. I write all the code, simply because none of our other IT pros are comfortable enough writing it. If there were "coders" under me to ask, please believe I would use them as a resource first.

slashdont. (1)

juuri (7678) | about 4 years ago | (#32845806)

Make the data type as precise as possible.

Now, try to make it more precise.

Indexes are like friends, you can have too many.

Don't do * querys.

If user MYSQL do everything in a view.

Re:slashdont. (0)

Anonymous Coward | about 4 years ago | (#32846602)

and normalize. Normalize. Normalize. ER modeling exists for a reason. Use it. It may be tempting to just stick everything into a single table with various columns stuck on to indicate relationships, inheritance etc, but then you might as well have a spreadsheet....

Re:Somewhere, a coder is polishing his resume (5, Insightful)

martin-boundary (547041) | about 4 years ago | (#32845836)

When you post a question on Slashdot, you have to expect teenagers who comment from their parents' basement to call bullshit on you based on their own extensive 30+ years of experience dealing with fortune 500 companies.

HTH.

Re:Somewhere, a coder is polishing his resume (0)

Anonymous Coward | about 4 years ago | (#32846552)

"When you post a question on Slashdot, you have to expect teenagers who comment from their parents' basement to call bullshit on you based on their own extensive 30+ years of experience dealing with fortune 500 companies. "

Yeah, sure, 30+ years of experience that are not enough neither to know quite a basic answer for quite a basic question nor how to search for it by himself.

No wonder others less fortunate not holding an "IT Director" title and wages will call "bullshit" and "if you don't know how to do your job, others like me will be glad to do it properly".

Do you know relational algebra? (3, Informative)

Cyberax (705495) | about 4 years ago | (#32845902)

Do you know relational algebra? If you don't, then I highly recommend:

Codd, E.F. (1990). The Relational Model for Database Management (Version 2 ed.). Addison Wesley Publishing Company. ISBN 0-201-14192-2.

It's MUCH better to know the fundamentals of database systems and then try to figure out details than vice-versa.http://ask.slashdot.org/story/10/07/08/2142211/Good-Database-Design-Books?art_pos=1#

Re:Somewhere, a coder is polishing his resume (0)

Anonymous Coward | about 4 years ago | (#32846216)

Hire a data analysis consultant to take a look at your data and come with some suggestions. A couple of days work that can save you weeks/months of headaches == money well spent.

Re:Somewhere, a coder is polishing his resume (0)

Anonymous Coward | about 4 years ago | (#32846474)

One other suggestion...
Look for user groups in your area for the brand of database that you're using. They can be a very valuable resource.

Re:Somewhere, a coder is polishing his resume (1)

m509272 (1286764) | about 4 years ago | (#32845604)

"I am the Director of IT for a small/medium sized marketing company, where I personally write the code that runs our applications"

Translation - I'm a one man IT department

Bud, start with the truth. A "Director of IT" does not write code. You could have equally said you were CIO, just as truthful.

Re:Somewhere, a coder is polishing his resume (0)

Anonymous Coward | about 4 years ago | (#32845782)

Agreed. Know how many companies I've seen with a "Director of Engineering", a "VP of Engineering", or a "CTO" with one or two direct engineering reports? I'd love to say that most of them were honest-to-goodness humble people who didn't let their titles go to their heads. Sadly, that often was not the case.

The OP is a Sysadmin who also codes some of the time. It's not about embarrassing you or debating your salary; it's about telling the truth of the matter.

Otherwise, I'm the CTO/IT Manager/Sysadmin/DBA/UED/Customer Service/Frontend/Midend/Backend ninja/wizard/jedi of my company. I do all those things, but I don't deserve those titles because I am not the same as someone hired to do those tasks. Otherwise, it's technically true. Especially the Jedi part.

Hm. (1)

fyngyrz (762201) | about 4 years ago | (#32846222)

As far as I'm concerned, if you wear the hat, you're entitled to the position. The entrepreneur who successfully pulls off multiple roles has hugely outperformed a company that requires a unique person in all of them. And s/he probably has a terrible headache, too, even if s/he loves the work, but that's a separate issue.

Re:Somewhere, a coder is polishing his resume (0)

Anonymous Coward | about 4 years ago | (#32845818)

your manners are poopy, and I'm not going to check this thread again, so you'll have to deal with me thinking that, and you not being able to change my mind with a scathing quip.

Mr. Projection (2)

luis_a_espinal (1810296) | about 4 years ago | (#32845966)

"I am the Director of IT for a small/medium sized marketing company, where I personally write the code that runs our applications"

Translation - I'm a one man IT department

Bud, start with the truth. A "Director of IT" does not write code. You could have equally said you were CIO, just as truthful.

That's an stupid, novice assumption. I've seen smaller operations with an IT director having to get down and dirty because of downsizing or lack of resources. Coding is done on top of the functions of IT managements.

So before you let your projecting ego go about correcting people's titles without knowing the specific circumstances, maybe you can try something more useful, like, oh I dunno, maybe answer the question and suggest a good relational modeling book. Crazy, I know!

Re:Mr. Projection (0)

Anonymous Coward | about 4 years ago | (#32846230)

Your manners are excellent and chivalrous. You have inspired a new race of knights. They shall congregate at a large round touchscreen, and chastise the infidels.

Re:Somewhere, a coder is polishing his resume (2, Informative)

DragonWriter (970822) | about 4 years ago | (#32845608)

There's a great chance that the article poster isn't like that. But I'm worried, because Ask Slashdot isn't who he should be asking... he should be asking the coders he manages how to design and/or restructure the database.

Why assume that there are any coders that OP manages? He's "Director of IT" for a "small/medium" company that isn't a software (or even technology) company. It's quite possible that OP manages, if anyone, a handful of desktop support technicians that aren't programmers.

In fact, I would hope that something like that is the case, as that's really the only explanation for a Director of IT that, as OP describes, personally "writes the code" (note: not "writes some of the code") for a company's applications, since otherwise he is managing coders that don't actually write any code, which would be unimaginably wasteful.

Certainly, I've known of small companies in non-computing fields where the "Director of IT" was also the whole IT department.

Re:Somewhere, a coder is polishing his resume (2, Informative)

kramulous (977841) | about 4 years ago | (#32845646)

I would say you are being a little paranoid. There is such a thing as a good boss, you know. I find that these are the guys who are still heavily involved in some sort of 'research'. Which is probably what he/she is doing. Probably a smart cookie, does some coding but by no means all of it. Knows enough to recognise a good text to buy for his group so they can all learn together.

I put it to you that I'd prefer to work with this guy than with your paranoid self. Do you have meetings of the secret type?

Re:Somewhere, a coder is polishing his resume (0, Offtopic)

obarthelemy (160321) | about 4 years ago | (#32845664)

+1

Re:Somewhere, a coder is polishing his resume (5, Insightful)

Anonymous Coward | about 4 years ago | (#32845670)

He was asking for a book, not your stupid criticism.

Re:Somewhere, a coder is polishing his resume (1)

rainmayun (842754) | about 4 years ago | (#32845704)

I wish I still had my mod points from yesterday, and I don't use them on ACs often. Titles are irrelevant. He didn't say anything about having a staff to manage - he might be the only person at the company who's capable of that kind of software development AND responsible for managing their network, desktops, phones, etc. And he may have no authority to hire anyone else. Sometimes you get stuck in a situation where you have to do what you can.

Re:Somewhere, a coder is polishing his resume (0, Flamebait)

Snap E Tom (128447) | about 4 years ago | (#32845720)

2010 and we still have the "I'm a Programming God So Get Out of my Way, Managers" mentality. Great.

Re:Somewhere, a coder is polishing his resume (1)

emurphy42 (631808) | about 4 years ago | (#32845768)

You're ass-u-ming that "Director of IT" means he manages other coders. He may be a one-man department, or may manage people in any number of other IT roles.

Um, dude, this is common at early-stage startups. (1)

Estanislao Martnez (203477) | about 4 years ago | (#32845864)

It's not uncommon for the chief of IT at a very early stage startup to be very technical and do a significant amount of coding in addition to management. At my company, the first chief of IT was one of the founders, along with two full-time programmers (though an extra two were hired a few months later). He coded less than the full-time coders, but he still wrote some key pieces of the early codebase, plus took care of a bunch of tasks like getting the first automated build system going so that the full-time developers could concentrate on other stuff.

Re:Somewhere, a coder is polishing his resume (1)

rcrow490 (1851796) | about 4 years ago | (#32846220)

You are an idiot!

Re:Somewhere, a coder is polishing his resume (1)

Nutria (679911) | about 4 years ago | (#32846310)

he should be asking the coders he manages how to design and/or restructure the database.

Hah!!!

Most (and I mean most) coders are HORRIBLE database designers. H-O-R-R-I-B-L-E.

That's why they're called code monkeys: because all they're good at is banging out mediocre code.

This is (1, Informative)

Anonymous Coward | about 4 years ago | (#32845506)

Don't be afraid of older books.. database theory really hasen't evolved that much.

For a good general overview you might check out "Database Design for Mere Mortals".

I'd also recommend you find a few books on specific areas

You'll probably want a book on normalization (preferably one not written by a normalization fanboy... there are times when de-normalized designs make sense) for sure as this speaks almost directly to the goal you described.

You'll probably want one on SQL tuning as well.. and one on modeling and documentation / diagramming.

Apprentice & dismissing the "normalization fan (1)

tlambert (566799) | about 4 years ago | (#32845794)

Apprentice & dismissing the "normalization fanboy"

-

As the head guy, it would probably be worthwhile for you to hire a skilled database person to sit down with you and pair-program over at least one design for a small project with you; this type of thing is the sort of thing you can pretty easily learn by doing/osmosis. Hiring a guy for a day is pretty cheap, as long as he understands he's there to help you get your sea legs, and that if you get really stuck, there might be a future day or so of work in it for him. In the long run, this will be better than a book.

-

When someone talks about normalization, they generally really mean "3rd normal form", i.e. without replication of non-key field data between records, or to put it in Bill Kent's words: "non-key fields in a record must provide a fact about the key, the whole key, and nothing but the key".

However, when you start talking about denormalization, you are actually probably talking about use of fourth and sometimes fifth normal form instead of third normal form. Unless you are data warehousing, you're not going to see any utility in sixth normal form.

This is still normalization, so you can't throw out the baby with the bathwater if third normal form doesn't apply to your problem.

Unless a database design book describes at least first through fifth normal forms, it's not a database book you want to buy. Scan the index of any book you might think you want to buy using Amazon or Google books, and if there isn't a section on 1NF-5NF normalization, then that book is not a candidate for you.

-- Terry

Denormalization (1)

DragonWriter (970822) | about 4 years ago | (#32846100)

However, when you start talking about denormalization, you are actually probably talking about use of fourth and sometimes fifth normal form instead of third normal form.

No, denormalization usually refers to the use of something that isn't 3NF (which rules out 4NF and higher normal forms, all of which are also 3NF), and often non-1NF schemas like star schemas.

4NF, 5NF, DKNF, 6NF are further normalizations beyond/within 3NF, not denormalizations.

Re:This is (1)

OneC0de (1851710) | about 4 years ago | (#32846070)

I actually had that book in my Amazon cart. I did see the publish date 10+ years ago though.

Database in Depth (5, Informative)

Anonymous Coward | about 4 years ago | (#32845550)

Database in Depth: Relational Theory for Practitioners
Publisher: O'Reilly Media; 1 edition (May 1, 2005)
Language: English
ISBN-10: 0596100124
ISBN-13: 978-0596100124

Best DB book i have ever owned/read/seen!

associative property of good (0)

Anonymous Coward | about 4 years ago | (#32845562)

A book about good designs or a good book about database design? Some meet one criteria but not the other.

Data Model Patterns: Conventions of Thought (0)

Anonymous Coward | about 4 years ago | (#32845564)

This is my all time favorite book on database design.

http://www.amazon.com/Data-Model-Patterns-Conventions-Thought/dp/0932633293

O'Reilly (3, Informative)

JWSmythe (446288) | about 4 years ago | (#32845566)

O'Reilly books are your friend. The "... in a Nutshell" books are a good place to start, and then proceed into the more advanced books. They have 25 titles related to MySQL [oreilly.com] and 53 titles related to Microsoft SQL [oreilly.com] . There are usually a few to browse through at the large chain book stores.

Re:O'Reilly (0)

Anonymous Coward | about 4 years ago | (#32845698)

Mod this up. O'Reilly are a little less dry than most books on relational databases. I used those books in school and I haven't come across anything I haven't been able to handle with the knowledge I've gained (and referenced!) from those books.

Re:O'Reilly (1)

Ethanol-fueled (1125189) | about 4 years ago | (#32846358)

Compared to the vast tutorials and examples found on the internet, O-Reilly books are relatively useless.

Buying them is like buying forty-dollar training wheels for the bike you learned to ride years ago.

A Director that still codes? (1, Interesting)

kramulous (977841) | about 4 years ago | (#32845582)

A director that still codes? What a novel concept. Good for you.

Re:A Director that still codes? (1)

obarthelemy (160321) | about 4 years ago | (#32845648)

is it ? reallllllly ?

Re:A Director that still codes? (1)

kramulous (977841) | about 4 years ago | (#32845748)

My assistant director and director barely know how to turn on a machine.

Not that they really need to know; Their job is more of people management. Making sure that departments talk to each other (and nicely) and that friendship bonds are formed and not destroyed. Conflict resolution, etc.

That is why I was surprised.

Re:A Director that still codes? (1)

bojangler (1851778) | about 4 years ago | (#32845692)

The "for you" is the important thing there. It may be good for his own practice and to stay fresh, but is good for the company?

Re:A Director that still codes? (1)

phyrexianshaw.ca (1265320) | about 4 years ago | (#32845734)

An IT director should remain comfortable covering any job position under himself, IMHO.

the day you try hiring a kid for his wonderful fiber terms and the kid stops showing up half way through a big rewire, without experience in the field you have a problem. when deploying a nation wide network covering the country on links with SA's longer than a roll of toilet paper, the IT director better damn well be comfortable working at the low level to troubleshoot and service that network.

putting a person in charge of IT for a company should NOT be a simple "tell these people what to do" it should be a position with delegation responsibilities. if the director doesn't keep up with the technologies he sells, how is he ever supposed to know how any of it works?

I make a guess and assume that any IT department you've ever had didn't have an R&D dept? nobody can progress forward if they only think they know how things work.

Re:A Director that still codes? (1)

kramulous (977841) | about 4 years ago | (#32845812)

I work in an area that is 100% R&D (along side twenty other coders).

IT departments really do vary in size. Ours is easily over 150 people.

My boss is still a coder and occasionally puts out a paper. He reports to the director who is a people manager and not an IT expert. And he shouldn't be one. He has 6 managers under him who know their stuff to give the directions. The director makes sure that everybody talks nicely to each other.

Re:A Director that still codes? (1)

OneC0de (1851710) | about 4 years ago | (#32845814)

lol

re: good db design books (0)

Anonymous Coward | about 4 years ago | (#32845616)

>"We have some tables that have million of rows, and I'd like to know the best method of designing these tables."

fantastic. If you ever decide to get into house-building, send me pictures.

Can you be more precise ? (3, Insightful)

obarthelemy (160321) | about 4 years ago | (#32845640)

I'm a bit unclear about what you want to achieve:
- easier end-user interface
- more reliability (backups, journalling, redundancy...)
- more speed
- more security
- more complicated data massaging (multi tables, statistics...)
- better vizualization (reports, graphs...)

I'm not sure a single book can cover all that.

Re:Can you be more precise ? (1)

phyrexianshaw.ca (1265320) | about 4 years ago | (#32845752)

I'd have to agree. it's hard to make a recommendation on what you should look at until we know what it is you want to accomplish?

if you're unhappy with your database, what about it are you unhappy with?

Re:Can you be more precise ? (1)

Ruke (857276) | about 4 years ago | (#32845758)

Maybe he can't - I can use databases, but I don't KNOW databases, and to be honest, I probably should. I don't know where the trade-offs lie in design and configuration, or even what I can really accomplish by tweaking them. If I had a million-row table, I'd just be concerned that, while it works right now, it may break due to a lack of foresight on my part, which is never a comfortable place to be in.

Re:Can you be more precise ? (1)

OneC0de (1851710) | about 4 years ago | (#32845978)

Exactly! I want to know, that if the business continues to boom for the next five years, my software won't fall apart, because of bad database design.

Re:Can you be more precise ? (1)

OneC0de (1851710) | about 4 years ago | (#32845970)

I'm looking to make sure our databases are configured for continuous growth. Our larger tables, I'd like to learn how to increase query time. I know how to create a table. I know how to run a query. I want to know, that I'm doing it right. ;)

Take a university class (2, Informative)

cduffy (652) | about 4 years ago | (#32845678)

I'm not sure I'd trust a book to teach this subject as comprehensively as a good university course on the subject. Frequently, you can sit a class quite inexpensively if you're not going for credit.

For that matter, isn't MIT or someone allowing free not-for-credit access to their eLearning materials?

MOD PARENT UP (1)

Vellmont (569020) | about 4 years ago | (#32845894)

I couldn't agree more. Database design isn't something you should learn from a book. You CAN of course, and it might work out, but you'd be much smarter to just take a class.

Let me put it this way. Programming is really mostly about data structures. Database structure tends to live for DECADES. Screw up the initial design, and you'll be hurting the business for decades to come. This isn't something to be taken lightly. A good DB design can pay off huge returns in the future when you have to add feature X. A bad design can be extremely expensive when you either can't implement whatever the new feature they want is, or to do so would be so difficult and compromise so much of the design and capabilities as to be useless.

Good SQL design books: (5, Informative)

8282now (583198) | about 4 years ago | (#32845710)

IMHO: Joe Celko's SQL for Smarties (http://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/0123693799/ref=sr_1_2?ie=UTF8&s=books) has shown itself to be very nice book when the need to go beyond the basics to a little deeper understanding of SQL is needed.

There are many other books on the subject all the way to source material from Date and Dodd but Celko seems to be well informed and writes fairly well, I think.

Re:Good SQL design books: (1)

8282now (583198) | about 4 years ago | (#32845716)

.... Sorry book!

Re:Good SQL design books: (1)

Jason Earl (1894) | about 4 years ago | (#32846078)

I was curious to see how far down I would have to read before Celko was mentioned. That's the book I would recommend.

Re:Good SQL design books: (1)

8282now (583198) | about 4 years ago | (#32846290)

I've enjoyed his book on trees and hierarchies in sql as well. Nice discussion of topics not often touched on in a lot of introductory db literature.

Newer Only Matters a Little (1)

j_f_chamblee (253315) | about 4 years ago | (#32845714)

Basic relational database design is about logic and structure. When compared with other areas of computing, I would argue that the the original materials worked out by Codd and Date have not changed nearly as dramatically. There are certainly exceptional sub-areas where there have been major changes (e.g. the introduction of the object model and development of XML and RDF, to name but two prominent examples), but if I were you, I would suggest doing two things:

1. Do some research into existing relational database platforms (e.g. SQL Server, Oracle, PostGRES), figure out which model will ultimately work best for you, and get a book on that implementation of SQL and RDMS. Every intro to databases book has a chapter on relational database design. That will get you started. You can also skip the platform selection process if you are already stuck with a database.

2. Read this: Date on Database: Writings 2000-2006 by C. J. Date [tinyurl.com]

Codd (now deceased) and Date are still the authorities, IMHO, and this latter book will give you a good overview of major conceptual issues in relational database design.

Best method (0)

Anonymous Coward | about 4 years ago | (#32845718)

MS Access has design wizards BUILT IN! Why would you need to ask /.?

Best book on the subject (1)

DeAgua (707093) | about 4 years ago | (#32845736)

The best book I know of on the subject is SQL Server 2008 Relational Database Design and Implementation by Louis Davidson. It's a complete reference for how to approach database design, with practical exmaples.

What's a book ? (1)

furgle (1825812) | about 4 years ago | (#32845750)

What is this book you speak of? Is it a new development platform? If so it must be new I haven't heard of it before. Is there a online tutorial on how to develop for this "book" technology?

Learn about database normalization (5, Insightful)

luis_a_espinal (1810296) | about 4 years ago | (#32845760)

"I am the Director of IT for a small/medium sized marketing company, where I personally write the code that runs our applications. We use a variety of technology at our office, the majority of which rely on MS-SQL and MySQL databases. I am familiar with tables, SQL queries, and have a general understanding of how the SQL databases work. What I'm looking for is a good book, particularly a newer book, to explain general database design techniques, and maybe explain some relational tables. We have some tables that have million of rows, and I'd like to know the best method of designing these tables."

There is more to RDBMS than tables and SQL. Your developers should understand data normalization first and foremost, at least 1NF, 2NF and 3NF.

http://en.wikipedia.org/wiki/Database_normalization [wikipedia.org]

http://en.wikipedia.org/wiki/First_normal_form [wikipedia.org]

http://en.wikipedia.org/wiki/Second_normal_form [wikipedia.org]

http://en.wikipedia.org/wiki/Third_normal_form [wikipedia.org]

The examples in the URLs above should suffice for getting a general understanding on how to start with a relational model. As for books, I'd suggest these:

http://www.amazon.com/Relational-Database-Design-Implementation-Third/dp/0123747309/ref=sr_1_4?ie=UTF8&s=books&qid=1278630155&sr=8-4 [amazon.com]

http://www.amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688/ref=sr_1_3?ie=UTF8&s=books&qid=1278630306&sr=1-3 [amazon.com]

I would also suggest C.J. Date's "Database in Depth: Relational Theory for Practitioners", but I can imagine the local penny arcade l33t-hax0r-wannabe crowd going batshit crazy about studying relational algebra and relational database theory in depth. To each his own. Most problems that arise in poorly designed relational database models arise from not understanding data normalization

:

modeling is even more important (0)

FranTaylor (164577) | about 4 years ago | (#32846140)

In the modern days of cheap disk, big disk caches, and large ram, proper modelling is more important than strict normalization.

Back when those books were written, disk was expensive and not cached, RAM was very expensive, and machines had terrible I/O bottlenecks.. Normalization is critical under these circumstances for maximum performance.

Today, these normalization techniques will increase performance but not as much as you might think. Really it is best to concentrate efforts elsewhere, especially for a one-person shop.

All of that normalization work requires coding changes and it will undoubtedly make the code much less readable and maintainable.

All you need to know about DB design... (0)

Anonymous Coward | about 4 years ago | (#32845762)

6 easy steps.

1) normalize 2) normalize 3) normalize 4) denormalize 5) denormalize 6) denormalize

Islamic law is for dumb fucks. Reject Allah. (-1, Flamebait)

Anonymous Coward | about 4 years ago | (#32845770)

Fuck Allah, Fuck Mohammad, Fuck Islam!!!

And if you're a Muslim? FUCK YOU!!!

Re:Islamic law is for dumb fucks. Reject Allah. (-1, Offtopic)

Anonymous Coward | about 4 years ago | (#32845886)

Fuck Allah, Fuck Mohammad, Fuck Islam!!! And if you're a Muslim? FUCK YOU!!!

Dear Infidel;

I am pleased to inform you of the fatwa that has just been placed upon your head. Please report to the nearest mullah for decapitation.

Yours in Allah,

OBL

OMG (2, Informative)

noz (253073) | about 4 years ago | (#32845786)

If you are designing anything bigger than a couple of gigabytes, you are in for some fun (or your users are). ;-)

To be a good designer, there is no substitute for a thorough understanding of the subject matter. And you are a self-confessed n00b. Get an expert. Or study. Hard.

Database in Depth: Relational Theory for Practitioners [amazon.com] .

Re:OMG (1)

OneC0de (1851710) | about 4 years ago | (#32846000)

One of our databases is 5GB in size, and gaining about 1GB every 2 months. I have gotten a few recommendations for the book you referenced, and I think it's only I'll be getting to "study hard"

Manga Guide to Databases. (3, Interesting)

MattBD (1157291) | about 4 years ago | (#32845874)

I did an exam on SQL and database design recently and used The Manga Guide to Databases as part of my studies. If you don't want something too rigorous it's very good indeed - I found it a lot better at making stuff sink in than a dry, stuffy book. It gives a reasonably good idea of things like the first, second and third normal forms. Don't be put off by the fact that it looks a bit childish - the storytelling idea really works well. It probably won't work for everyone, but it did work well for me (I passed the exam with flying colours).

Normalized vs Denormalized (1, Informative)

Anonymous Coward | about 4 years ago | (#32845924)

Depends on what you want to do with your database. You have two broad options:

Normalized database:
Application developers prefer this because you'll design your database where every discrete list has its own table. The main benefits are performance and maintainability. For example, if you are tracking a list of marketing promotions and they each have a status of "Started" or "Finished", the statuses "Started" and "Finished" would be in one table and the table holding the promotions would have foreign key relationships to it. Your developers could then, instead of using string matching for "Started", filter on the foreign key of 1 (or whatever the integer key is). There's a big performance boost there. Also, you'll be able to rename and/or add to those statuses without affecting the underlying data.

De-normalized database (data warehouse):
If you are talking about 1 million+ rows you're probably going to be interested in doing analytics and reporting. The data warehouse (look for Kimball: The datawarehouse toolkit) is designed for report writers and analysts in mind. For a marketing example, perhaps you have a Promotions "Dimension" where all the attributes about a certain promotion are described (region, name, type, current status, client, etc) and one or more "Fact" tables that describe the metrics you want to measure about Promotions. For example, length of promotion, units sold, etc. This type of database structure makes it easy for people who are not SQL experts to explore and analyze the data. Data warehouses are usually produced out of an ETL (extract, transfer, load) process that copies data for a normalized database (usually because that is what the application is using).

Hope that helps.

Re:Normalized vs Denormalized (1)

Hecatonchires (231908) | about 4 years ago | (#32846252)

+1

Normalised is normally used for applications in use

De-normalised (flattened) is used for historical (trend) reporting

Text Book (2, Informative)

pgn674 (995941) | about 4 years ago | (#32845946)

My university course on databases used the text book A First Course in Database Systems by Jeff Ullman and Jennifer Widom. I rather enjoyed the book, and plan to have it above my desk in case any sort of database design or maintenance project comes up for me. The book's page is here [stanford.edu] ; links to purchase are at the bottom.

forget DB books (0, Flamebait)

stanlyb (1839382) | about 4 years ago | (#32845950)

My experience shows that when you are dealing with very big tables, or very frequent requests, or whatever else extraordinary, then all the "standard" advises are simply NOT WORKING. The only solutions is to see how the other are doing it, or to hire some DB guru, even if it is for 3-6-12 months contract, and let him rearrange and restructure your DB. Just for example, if have very very big tables, the only working solutions that i am aware of is to forget about CONSTRAINS, FOREIGN KEYS, or whatever little else convenient extra that every db book could have advise you. In fact, you should use ONLY PRIMARY KEY. Also, you should use physical partitioning, based on some criteria (like Oracle is doing it), and finally, you should have some temporary tables, containing some useful pre-calculated data.

Re:forget DB books (1)

afidel (530433) | about 4 years ago | (#32846226)

A few million rows is a small database to a RDBMS like SQL Server, Oracle or Postgres.

the wrong model (0)

FranTaylor (164577) | about 4 years ago | (#32845972)

For many aspects of coding, you are barking up the wrong tree. You should really concentrate your design thinking at the object persistence level instead of the database level. Use Hibernate or something similar to hide the database from your code.

Then you can take a critical look at your choices of database vendors, and you will be easily able to migrate your code the one whose product best fits your needs. You will be able to run your own benchmarks with your own data and see what will work best for you. You may even find that an object-based database will give you far better performance for the same coding effort.

Would you like to reduce your development time?.. (3, Informative)

meburke (736645) | about 4 years ago | (#32845984)

...and improve your quality and maintainability?

Back in the 70's and early 80's we learned a methodology called, "Data Structured Systems Design" and the fundamental presupposition was that everything could be expressed logically and accurately by describing it as relationships in set theory. I have not seen anything since that surpasses the quality and maintainability of database applications and systems.

Someone already mentioned Joe Celko's book "SQL for Smarties" and I would recommend you first read his, "Thinking in Sets" before any of his other books.

I would also suggest some earlier books by Ken Orr and Jean Dominique Warnier. If you learn the Warnier-Orr approach to DESIGNING the system before doing any coding, you will reduce the time necessary for maintaining the system. I have seen hundreds of small IT shops like yours, and much of the time Systems Analysis and Design is neglected and performed "off-the-cuff" by programmers who can't wait to get to the coding. I didn't originally believe Ken Orr's assertion that spending twice as much time designing the system would result in a sharp time reduction for overall project completion, but through experience and observation I became a believer.

Get involved in PASS... (1)

misfit815 (875442) | about 4 years ago | (#32845998)

...and other like-minded groups. You're going to learn more from interactions with other DBA's than from any book. I'm a dev at a place that can measure db growth in TB/week, and have learned a tremendous amount just from working with DBA's in our organization.

Look at PPDM for real-world huge designs (0)

Anonymous Coward | about 4 years ago | (#32846006)

Take a look at Professional Petroleum Data Management (PPDM). They are a consortium of over 100 companies who created data models used throughout the petroleum industry. Their documentation and standards are top-notch:

http://www.ppdm.org/standards/model/lite_v1/archive.html
http://www.ppdm.org/standards/model/lite_v1/documentation/html/index.html

Database Design for Mere Mortals (1)

condition-label-red (657497) | about 4 years ago | (#32846052)

I found "Database Design for Mere Mortals" (ISBN 0-201-69471-9 [wikipedia.org] ) to be an good/easy entry point for good database design methodology.

Re:Database Design for Mere Mortals (1)

KFW (3689) | about 4 years ago | (#32846260)

I second that. Not extremely technical, but a good first read about relational databases, normalizing, etc. /K

Good Database Design (1)

mosb1000 (710161) | about 4 years ago | (#32846090)

I work with databases a lot, and while I've read books on design, I don't think it's complex with a relational database since most of the design has already been done for you. The guiding principles I follow are simple:

1) Don't over-complicate your database. Only store data that you plan on using or you need to store, stay away from adding unnecessary tables or data. Don't try to build a fancy user interface with lots of code unless you expect the database to be used by a layperson, and only build such interfaces with a specific scope in mind. If the user wants to do something more complex, let them come to you. A knowledgeable user should be able to understand and use your database without an interface. If they can't you've probably done something wrong.

2) Don't abbreviate your field names. A modern relational database can handle spaces and long names. But a modern user still can not guess what your abbreviation meant. Yes, it makes the code a little longer, but you're better off in the end.

3) Don't use an arbitrary primary key unless you have a good reason to. If you are storing data that is meaningful, it will also be unique, so you may be able to find one or two fields that can serve as a primary key on their own. This makes it easier for people to navigate/maintain/update the database later on because it keeps the number of fields down.

4) Don't store data in more than one place, that way if you need to update it you only have to change it once.

5) Lock down your data. Have a good idea about all the possible data and potential field values and don't let people enter data that your database isn't designed to handle. If there's a field with a finite number of possible entries, store those possible values in a separate table and require the field to draw it's value from that table.

Three practical lessons (5, Informative)

Invisible Now (525401) | about 4 years ago | (#32846376)

These three lessons may not all be in any one book, but they can help in the real world:

1) Learn what SQL Injection is and how to defend against it. It will ruin your day and could severely damage your current employment situation.

2) Abstract your schema from your front-end applications. Stored procedures are easy to write and can provide security and if well written stop injection attacks. They will let you change your database design without breaking your deployed apps. Just update the internal code in the P. Middleware and objects can do this, too.

3) Bergstrom's law of sailing says: "You can get away with anything in less than 5 knots of wind." Similarly, any little box or blade with 2 to 4 gs of RAM can easily handle 5 to 10 million row tables. Dedicate the server to MySQL or MS SQL so they can cache and buffer efficiently and they will outperform much bigger boxes trying to run too many schemas and DBs concurrently. Learn to index. Don't be too puritanical about normalization. Returning a customer address should require 6 joins. And remember that moving that moving large recordsets across the LANWAN may take much more time than the server query.

You probably already know all this... but maybe someone else reading this doesn't.

Re:Three practical lessons (1)

Invisible Now (525401) | about 4 years ago | (#32846396)

"Returning a customer address " SHOULDN'T "require 6 joins."

My bad!

Date (1)

Obfuscant (592200) | about 4 years ago | (#32846582)

An Introduction to Database Systems, C.J. Date.

He's like the grandfather of relational database systems. Quel truly is the language of the Gods.

Load More Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...