Questions to Ask a Client When Designing a Database
Questions to ask a client when designing a database, for the developer, analyst, freelancer or student who has to gather requirements before drawing a single table. They run in the order a requirements interview usually takes: how information is kept today, the things to record and how they connect, who will use the system, what they need out of it and how much it has to hold, the rules and their exceptions, the old data that has to move, and then access, backups, hosting, budget and upkeep. Put each one in the client's own nouns and keep the database vocabulary for your notes.
Want questions from the whole vault instead? Try the random question generator.
The questions
Each question, and why to ask it
Today
What do you need this database to do that you cannot do today?
Why ask it
The answer is the yardstick for every design choice that follows. If the client replies with a feature, such as 'a screen for orders', ask what goes wrong without it. The problem often points to a different table than the feature does.
What information does the business keep track of right now, and where does each piece live?
Why ask it
Expect a mix of spreadsheets, paper, email and one person's memory. List every place with the name of whoever looks after it. Each one becomes either a source to migrate or a habit the new system has to replace.
Can you walk me through one job from start to finish, such as a single order, booking or case?
Why ask it
Following one real item end to end turns up the tables in the order they get filled. Note every moment somebody writes something down or looks something up, and who that somebody was.
Which spreadsheets, paper forms and printed reports can I take a copy of?
Why ask it
The column headings on these are a first draft of your field list. Ask for filled-in examples, not blank templates: the notes squeezed into the margins show what the form had no box for.
What goes wrong most often with the way things are recorded now?
Why ask it
Listen for duplicates, two versions of one file, and updates that never reached everyone. Each complaint maps to a design decision: a unique key, one home for each fact, or a record of who changed what.
What does the current setup do well that you would not want to lose?
Why ask it
A spreadsheet lets anyone sort, filter or add a column in a minute, and people miss that once it is gone. Whatever the client names here goes on the requirements list beside the new features.
Has anyone tried to build or buy something for this before, and what became of it?
Why ask it
An abandoned system may still hold data worth importing. Ask why people stopped using it. When the reason is the daily effort of keeping it filled in, your design has the same hurdle to clear.
Records
What are the main things you keep records about: customers, products, jobs, staff, something else?
Why ask it
The nouns in the answer are your candidate tables. Read the list back and ask which one the others hang from. It is often the thing the business bills for.
For each of those things, what do you need to know about it?
Why ask it
Take them one at a time and ask what the client would write on an index card for it. A detail that really describes something else, like the customer's address on an order, is a link to another table and not a field.
How do you tell two of them apart: a number, a code, or only the name?
Why ask it
This is the primary key question in plain words. If the answer is the name, ask what happens when two customers are both called John Smith. If a code already exists, find out who issues it and whether one has ever been reused or changed.
Can one customer have several orders, and can one order ever belong to more than one customer?
Why ask it
Swap in the client's own nouns and ask in both directions for every pair that seems connected. The word 'ever' matters. A yes that comes up once a year is still a many-to-many relationship and needs a linking table.
Which details can have more than one value, such as phone numbers, addresses or contact people?
Why ask it
Clients picture one box per detail until someone asks. Follow with 'what is the most you have seen for one account?' and whether one of them counts as the main one.
Which details change over time, and do you need to see what they used to be?
Why ask it
Prices, addresses, job titles and statuses are the usual suspects. If an old invoice has to show the price charged that day, the price belongs on the invoice line, or the price table needs effective dates.
What stages does an order or a job pass through, and who moves it from one to the next?
Why ask it
The stage names become your status list, and the handoffs show who needs edit rights. Ask whether a record can go backward or skip a stage, because that decides how strictly the status can be enforced.
Which details should be picked from a set list, and who is allowed to add to that list?
Why ask it
Free-text categories end up as five spellings of the same thing, which wrecks any report that groups by them. A list the client wants to edit themselves needs its own table and a screen to manage it.
Are there files that go with a record, such as photos, signed forms or scans?
Why ask it
Find out the typical size, how many per record, and whether the contents must be searchable or only opened. A few thousand photos can outweigh every other table put together, and that changes where the system can sensibly be hosted.
Do people here use different words for the same thing, or one word for two different things?
Why ask it
'Client', 'customer' and 'account' may be one table or three. Settle the vocabulary in the meeting and keep a short glossary. Table names that match what the staff say need no translating later.
Is anything recorded in more than one currency, unit of measure, language or time zone?
Why ask it
One yes here adds a column beside every amount or time it touches: the currency next to the price, the unit next to the quantity. For times, find out whose clock a booking is read on, the customer's or the office's.
What do you calculate from the data, such as totals, balances, ages or days overdue?
Why ask it
Get the formula in the client's words plus one worked example with real figures. A value that can be recomputed usually should not be stored, unless it has to stay frozen as it was on the day, like an invoice total.
Is there anything you never recorded and later wished you had?
Why ask it
A new detail is cheap to add today and hard to fill in for three years of old records. Test each wish by asking who would type it in and at what moment. A box nobody fills in is worse than no box.
Use and scale
Who will use the database, and what does each of them do in it on a normal day?
Why ask it
Collect roles, not names: front desk, bookkeeper, owner. Whoever enters the most data should be in a later meeting, since that person knows awkward cases the owner has never seen.
Which reports do you need, and can you show me one you rely on now?
Why ask it
A real report is the best test a design can get, because every column on it has to trace back to something you plan to store. Ask how it is sorted, grouped and totaled, who reads it, and how often it is run.
What do you wish you could find out about the business but currently cannot?
Why ask it
These are the answers that justify the project. Check each against your list of things and details. 'Which referral source brings our best customers' needs a referral field that nobody mentioned an hour ago.
When you look up a record, what do you search by?
Why ask it
Name, phone number, ZIP code, the last digits of an order number: these are the columns to index. Ask whether a partial match or a misspelling still has to find the record, since that changes how the values are stored.
How will information get in: typed by staff, filled in by customers, imported from a file, or sent by another system?
Why ask it
Each route needs its own checks. What customers and imports send is messier than what a trained clerk types, so those rules have to live in the database itself and not only on a form.
Can you sketch the screen you would want for the task you do most often?
Why ask it
A rough drawing on paper is enough. The order of the boxes shows the order facts arrive during a real phone call or visit, and which ones are not known until later and so cannot be required at the start.
Will several people work in it at once, and could two of them open the same record?
Why ask it
This settles early whether a shared file will do or a server database is needed. Ask what should happen when two edits collide: the later one wins, the second person gets a warning, or the record locks.
Does it have to work away from the office, on a phone, or with no connection at all?
Why ask it
Offline use is the costly one, because it means local copies and conflicts when they sync. Ask how often it truly happens and whether a paper note typed in afterward would be acceptable.
Roughly how many records of each kind exist now, and how many are added in a typical week?
Why ask it
Orders of magnitude are enough: hundreds, thousands, millions. Pin down the largest table and the busiest season, because that pair decides which product can cope and where indexing matters.
How do you expect that to change in the next few years: more locations, more products, more staff?
Why ask it
A second branch or a second company becomes a column on nearly every table, simple to include now and painful to retrofit. Leave room for plans the client is sure of and only write down the ones they hope for.
Does the database need to swap data with other software, such as accounting, a website or an email tool?
Why ask it
For each fact both systems hold, such as a customer's address, agree which system is the master. Get a sample export or the interface documentation from the other product before you promise a connection.
Rules
Before an order or a booking can be saved, what does it always have to include?
Why ask it
'An order needs a customer' and 'a booking ends after it starts' turn into required fields, checks and foreign keys. For each rule ask 'always, or nearly always?' and note the cases that make it nearly.
What should the system refuse to let someone do?
Why ask it
Typical replies are deleting a customer who still owes money, booking one room twice, or selling stock that is not there. Rules told as stories of past mistakes are the reliable ones, so ask what happened last time.
What are the odd cases that do not fit the usual pattern?
Why ask it
The customer billed through a parent company and the product sold by weight both live here. Ask how often each one comes up, then decide together whether the design models it or a notes field carries it.
When a detail is left blank, does that mean you do not know it yet, or that there is none?
Why ask it
A missing middle name and a missing delivery date are different kinds of empty, and a report that counts blanks will mix them up. Where the difference matters, plan a separate 'none' or 'does not apply' choice. Anything unknown at the moment of entry cannot be mandatory, or staff will type junk to get past the screen.
When a record is no longer needed, is it deleted, archived or kept for good?
Why ask it
Clients often say delete and mean hide. A true delete breaks old reports, so offer an inactive flag. If a regulator or contract sets how long records must be kept, or how soon they must be erased, ask the client to confirm the period with whoever advises them.
Do you need a log of who changed a record, when, and what it said before?
Why ask it
An audit trail is easy to plan for and miserable to bolt on. Agree which records it matters for, since logging everything costs storage and most of it is never read. Money and anything a customer might dispute tend to head the list.
Which rules are likely to change, such as tax rates, discount levels or price bands?
Why ask it
Anything that moves belongs in a table the client can edit, with a start date on each row, and never inside a formula or a line of code. Ask what had to be done the last time one of them changed.
What should happen when a new entry looks like one that already exists?
Why ask it
The same person keyed in twice with a slightly different spelling is the classic case. Find out whether to block it, warn, or allow it and merge later, and which details together convince the client it is the same one.
Existing data
Which of your existing data has to come into the new database, and how far back?
Why ask it
Not everything has to move. Closed jobs from ten years ago can often stay in the old file as a read-only archive. Every extra year of history adds cleanup, so agree on a cutoff date.
How clean is that data: duplicates, blanks, inconsistent spellings, dates typed as text?
Why ask it
'Pretty clean' is the usual reply and rarely the whole story. Get a copy and profile it yourself before quoting the migration. Counting the distinct values in a column that should hold ten options is a fast first test.
When two records disagree, who decides which one is right?
Why ask it
Migration throws up conflicts that only someone inside the business can settle. Get that person named now, along with the hours they can spare, or the cleanup will stall waiting for answers.
Can I have a full copy of the current files to test with, and is there anything in them I should not see?
Why ask it
Designing against real rows catches what tidy sample data hides. If the files hold personal or financial details, agree in writing where your copy is kept and when it gets deleted, or ask for a version with those columns masked.
Are there codes, abbreviations or color markings in the current files that only some people understand?
Why ask it
A yellow row or an X in the last column often carries a status that is written down nowhere else. Sit with whoever invented the marking and turn each one into a proper field before the import.
Once the new database is live, what happens to the old files?
Why ask it
Left editable, they keep getting used and the business ends up with two versions of the truth. Agree on the day they become read-only and who tells the staff.
Running it
Who should be able to see, add, change and delete each kind of information?
Why ask it
Work through it as a grid of roles against tables. Ask by name about pay, health details and private notes on people, which is where 'everyone can see everything' tends to stop being true.
Does the database hold personal, financial or health information, and which rules apply to it where you operate?
Why ask it
Do not answer this for the client. The rules differ by country, state and industry. Ask who advises the business on them, and record what that person says about encryption, retention and where data may be stored as requirements.
If the data vanished tomorrow, how much work could you afford to redo, and how long could you manage without it?
Why ask it
The two answers are the backup schedule and the recovery time, stated in the client's terms. Losing an hour and losing a day lead to different setups at different prices. Ask who will test that a backup really restores.
Where do you want it to run: a computer in the office, your own server, or a hosted service?
Why ask it
Plenty of clients have no view, so ask who looks after their computers now and what software they already pay for. Compare the options on monthly cost and on who fixes it first thing Monday. Ask any provider exactly what its backups and uptime terms cover.
Is there a set amount for the build, and a separate one for hosting, licenses and support in the years after?
Why ask it
The yearly costs keep arriving long after the build is paid for. If the client has only a build figure in mind, show running costs as a separate line in the proposal so the first renewal is not a surprise.
When does it need to be up and running, and what is that date tied to?
Why ask it
A financial year end, an audit and a busy season each make a different kind of deadline. If the date cannot move, agree which records and reports must work on day one and which can follow, then count backward to the day the old data has to be clean.
After handover, who will add users, fix problems and make small changes?
Why ask it
If it is someone on staff with no technical background, lean toward editable lookup tables and simple admin screens. If it is you, settle the rate and how fast you are expected to respond before the build starts.
What documentation and training do you expect at the end?
Why ask it
A table diagram, a data dictionary and a one-page how-to for each role is a reasonable set to offer. Ask who will train a new hire a year from now, because that person needs the fullest version.
Who gives the final go-ahead on the design, and what would you like to look at when you review it?
Why ask it
Few clients can read an entity diagram. Mock screens or a spreadsheet laid out like the tables, loaded with their own rows, draw far better comments. One named approver keeps changes from arriving from three directions.
Once people have used it for a few months, how will we tell that the database is doing its job?
Why ask it
Push for something you can check: the month-end report takes minutes, nothing is typed twice, a lookup takes one search. Put the answer in the proposal as the acceptance tests.
How to run a database requirements interview
Practical guidance for the conversation itself
Before you meet the client
Collect the paperwork ahead of time
Ask for every spreadsheet, paper form and printed report a few days before the meeting, filled in with real entries. An hour spent reading them lets you skip the questions they already answer and arrive with sharper ones, such as why one sheet has two columns both headed Date.
Ask for the person who does the typing
The owner knows what the business wants out of the system. The person who keeps the current records knows what really goes into them, including the workarounds. Try to get both in the room for the Records and Rules groups, or book a second, shorter session with the one who was missing.
Decide what goes in writing
Counts of records, the list of other software and the question about where it should run are lookups. Send those by email so the client can check and reply when convenient. Keep the meeting for anything that needs a follow-up: the walk through one job, the relationships between things and the exceptions.
If this is a class project
Students often have a made-up client or a classmate playing one. Run the interview anyway, write down the answers, and hand in the notes with the diagram. The Records and Rules groups map directly onto the entities, relationships and constraints a design assignment usually asks you to show.
While the client is talking
Leave the jargon in your notebook
A client who is asked about entities, cardinality or normalization will either guess or go quiet. Ask about 'things you keep records on', 'how many of these can one of those have' and 'where is that written down'. Translate into tables and keys afterward, on your own time.
Write down nouns and verbs
As the client describes a job, the nouns are candidate tables and details, and the verbs are the events that create or change rows: a customer places an order, a technician closes a ticket. A verb with a date and a person attached usually deserves a table to itself.
Ask for a real example every time
'A customer can have several addresses' is a rule. 'Show me the customer with the most addresses' is evidence, and it tends to reveal a billing address, a delivery address and a vacation home that nobody would have listed from memory.
Chase the words usually and normally
Each time the client says usually, normally or most of the time, stop and ask about the rest. The Rules group exists for this. A database has to hold the unusual row as well as the common one, or people go back to keeping a side spreadsheet.
Draw it where they can see
Boxes with the client's own words in them and plain lines between the boxes are enough. Say each line aloud as a sentence, 'one job has many visits, each visit belongs to one job', and let the client correct you. People spot a wrong sentence much faster than a wrong diagram.
Turning the answers into a design
Send back a one-page summary
Within a day or two, send the list of things to be recorded, the rules you heard and the reports the system must produce, all in the client's vocabulary. Ask them to mark what is wrong or missing. A correction at this stage costs a sentence. After the build it costs a migration.
Test the draft against their reports
Take each report the client handed over and trace every column to a table and field in your draft. Any column you cannot trace is a missing requirement. Then take the search answers and check that each lookup can be done without scanning everything.
Load a slice of the real data
Before the design is approved, import a few hundred real rows from the existing files. The rows that refuse to load are the exceptions nobody mentioned. Take that list back to the client as questions and resist fixing them quietly.
Sort rules into enforced and advised
Some rules belong in the database as keys, required fields and checks. Others are better as a warning on screen that a person can override, such as an order above a credit limit. Go through the Rules answers with the client and agree which kind each one is.
Mistakes that force a redesign
Copying the spreadsheet column for column
The existing sheet shows what is recorded, not how it should be stored. Columns named Phone 1, Phone 2 and Phone 3, or a customer's name repeated on every order row, are signs of a second table hiding inside the first.
Designing only for the reports asked for
Reports change as soon as the client sees the first ones. If the facts are stored once, at the finest level anyone records them, new reports are a query away. A table shaped around one monthly summary cannot answer the next question.
Leaving the old data until the end
Migration is where estimates go wrong, because nobody knows how messy the files are until someone looks. Ask the Existing data questions in the first meeting and price the cleanup as its own item.
Answering the privacy question yourself
What may be stored, for how long and where depends on the place and the industry, and it is not something to settle from memory in a meeting. Find out who advises the client, record what that person says as a requirement and build to it. It is worth asking the same adviser whether holding a copy of the data puts any duties on you. If nobody advises them, say so in writing and suggest they find out before launch.