November 20, 2014 at 2:44 pm
Hi all,
You've heard of the Developer who becomes the 'Accidental DBA', well now I'm the first guy doing it in reverse 🙂
I've been asked by a friend to help design the backend of his new solution, as a DBA I have very little experience of design, but I think it's a good first project.
He already has a partial design in place, I suspect his previous Developer left him with a few ideas, now he's asking if I can help him through. He wants as bare-bones a system as possible, basically a system that shops can sell stock to each other, buy logging into and either complete a 'Want to sell' form or a 'Want to buy' form, thus adding a row/listing to either. Every article is generated a new unique ID by the DB merging its manufacturer, colour, size and a few other criteria.
Any guidance on what questions I should ask, or what the best way forward is?
JS
November 21, 2014 at 5:48 am
A few thoughts.
1) Ask him what the primary purpose of his program is. He should be able to tell you this in one sentence. If he can't, then he hasn't thought it through enough and you're about to walk into a big giant mess.
2) Before you start designing, understand every last bit of data he intends to collect. Not just now, but for future expansions. The database should be designed for scalability. Basically, you'll want to avoid redesigning the schema every time a scope change comes in.
3) Don't let him design the database or dictate how it is designed if he has never had experience designing databases in a high transaction environment. He will more than likely design a database that makes it easy for him to program against rather than designing a database for high reads or high writes.
4) Research normalization. You're never going to get a true 5th Normal Form database. Business rules will eventually require compromise. But you can design a fairly well-normalized database that will help performance rather than kill it.
5) Do design in sprints aka small deliverables. Don't try to design the whole thing in one go. Design a base, test it, redesign as necessary.
After years of learning the hard way, I tend to focus on design formats that can be reused. For instance, we collect data from multiple systems and multiple types of customers. One of our older systems has a different table for each and every type of customer. Our newer EDS store (that I designed) has a single Entity table for all customer basics, a single Address table with effective dates for address changes, and then a separate table (if needed) for customer types that have characteristics not shared by the other customer types.
Ask him about historical data retention. Does he need it? For how long? How does he anticipate dealing with data change (addresses, emails, phone numbers, etc.)?
Design discussion is also a good time to bring up things like availability, disaster recovery, backup plans too. If you don't include them now, then database you design might not lend itself to the solution he needs for these things.
I hope these thoughts help.
November 21, 2014 at 1:20 pm
He already has a partial design in place, I suspect his previous Developer left him with a few ideas, now he's asking if I can help him through.
First, start over with the design. You'll just give yourself headaches trying to use a "partial design" from a developer. All developers somehow think they can design dbs, which in fact (virtually) none of them can!
Don't get fancy. Follow the normal steps. Identity major entities and attributes. Don't initially worry about putting them in tables, just get as good and thorough a list as you can.
Then do the standard 1nf, 2nf, and 3nf phases. End with bcnf, or not, or only part of it, whichever you prefer as a DBA. Don't try to short cut either. Normalization is a step-by-step process. That doesn't necessarily mean it's slow, but it's mostly a serial process, not an unordered one.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply