I have somehow landed myself with the job of creating a database to drive an Interactive Voice Response (IVR) system and am struggling to get started.

I was wondering if anyone here is a SQL guru and would be willing to give me some pointers or, failing that, point me to an on-line forum where I could post my question which would be helpful to a newbie – i.e. not Stack Overflow!

Thanks!

Do you

a) understand database design
and/or
b) understand SQL

?

Its hard to say where to start with this.
Have you any DB experience at all? What db are you working with.

I’m not a database designer as such, but I have just finished a project designing a database.

Unless you have an existing database of some sort to use as a starting point, I would start with some pictures to get your ideas in shape.

What is the data you are working with? This will be mapped onto tables, but you need to understand the relationships between them.

What are your clients? I’ve got a mixture of humans (accessed via a web interface) and servers spewing data at me at a rate of knots.

Deadlocks, they’re fun.

Testing. How will that work? I’m using gitlab and pre-submit checks and cannot recommend it enough.

Feel free to message me.

Thanks for the responses. it is SQL Server 2014. I am familiar with SQL database basics.

My issue is that I need to create a list of destinations that could be:

Announcements
Menus
Queues
Transfers

These all have their own tables with columns specific to their type. This list will be used to define a destination for a trigger number.

So i will have a “TriggerNumber” table with pkid of four digits and I want it to point at an annoucement, menu, queue or transfer. I have bodged something using a view which lists the GUIDs for the four destination type records in a single column but I am not really happy with this.

What I really want is some form of intermediate table where I can store all destination types with a primary key field that I can reference in the triggernumber table.

I’m a DBA of 20 years. DBAs generally don’t like developers…

anyway, you’ve a master table, and then you’ve a foreign key lookup to the various destinations?

All of that can go in one table; just have the primary key, type, actual value. Why do you need an intermediate table?

But PM if you want to go into more detail etc

I can see two approaches here.

1) Step away from the keyboard and design the database on paper. Tables, fields, back when I last did anything like this in anger about 20 years ago it was called an “Entity Relationship Diagram.” Don’t write a line of code until you have this and have it approved by Them Above.

2) Suggest that what they really need here is a DBA. Good, efficient database design is an art not a science, and if you go down this road you’ll likely wind up with something not fit for purpose that you’ll be battling with for years. I’d totally be comfortable in rolling my own database for personal use, but no way in hell would I entertain the idea of designing a production system. It’s the difference between building a garden wall and building a house.

I think this question covers what you’re trying to do, and the first answer gives a good list of options:

https://stackoverflow.com/questions/7844460/foreign-key-to-multiple-tables

If you go for the first option (multiple FK columns), you can use a constraint to check that exactly one of the FK columns is not null:

https://dba.stackexchange.com/questions/5278/check-constraint-only-one-of-three-columns-is-non-null

I’m with Cougar;

Design what you want on paper as an ERD. Get approval.

Let someone who understands DB’s (and SQL) build and create the actual db.

If this is a production system you want it right or it’ll add costs throughout the product life cycle trying to work round any shortfalls.

What’s the consumer for this DB? As a coder, I’m going to break ranks and say get an experienced DBA in to do it. It really is an art, and bad DB design will lead to a nightmare in support for both you and anything consuming the DB.

DBAs generally don’t like developers…

The feeling is mutual 🙂
I would join in the general thing though of saying get someone more experienced involved. Although I wouldnt go with the assumption a DBA is the right choice. Needs to be the right sort of DBA. Several I know could look after the server but could do bugger all else in terms of actually designing databases

DBAs generally don’t like developers…

DBAs are the semi-domesticated chumps that set up database back-up scripts that run every night but take longer than 24 hours to complete, see the problem in the log but ignore that, and instead blame the developer when the app gets slow due to the 100 concurrent backup jobs still running.

Bring it 🦡

eta: i’m not racist some of my best friends are DBAs

Needs to be the right sort of DBA. Several I know could look after the server but could do bugger all else in terms of actually designing databases

That would be me, and I would very much not describe myself as a DBA.

semi-domesticated chumps

we love you to.

however 24 hours for a backup sounds like you need a new DBA. But if a second backup tried to run when the first is already running, it would say no, i’m already doing it. If it’s 100 different DBs backing up at the same time, it should be able to cope with it

I’m a 15 year SQL Server DBA and developer.

Mostly people have told you the main things about design, or where to get help, but I’ll add one thing that may not have occurred to you:

When you’re developing application code, and something doesn’t work how you want it to, you can change the code, deploy it in place of the old stuff, and hey presto it works.

You CANNOT do this with database development. When you do a deployment, you can’t slash and burn the existing data. Whenever you make a change in your dev environment, you have to already be thinking about how you will make this change in production while preserving the existing data. You might have to transform the existing data somehow, you might have to fill in some gaps, or you might just need to protect it from alteration. You might be able to get away with doing nothing at all, but you have to know you can get away with that.
Data is possibly the most valuable asset your employer has, and you can’t afford to lose it.

It’s a different development paradigm altogether, and it’s vital you get into that mindset from the very start. A lot of devs that I work with just do not get the concept at all, and it infuriates me.

As a stopgap, can I suggest the following simple two column, 1 data row table

Tone_pressed…….Action
Any…………….Play greensleeves

Edit. Damn you and your white space destruction …….

Thanks for the responses – especially pdw whose link was useful.

I know that this is not an ideal scenario – basically the scenario is I have to show that using DB queries is a viable approach to solve the issue we have been handed and, if I get that far, we would engage a qualified DB designer to take what I have cobbled together and redo it properly.

My version will never be a production system – it is more about proving that the IVR system will work ok when being driven by SQL queries.

Ah, so it’s just a proof of concept? That makes a lot more sense.

A word to the wise here then: don’t make it work too well. (-:

petec

we love you to.

😉 you know we ❤ you guys really

however 24 hours for a backup sounds like you need a new DBA. But if a second backup tried to run when the first is already running, it would say no, i’m already doing it. If it’s 100 different DBs backing up at the same time, it should be able to cope with it

Not our DBA, our software was installed at a customer’s office. The database was Oracle, I don’t know how backups work on that but I do know they were run by some monster script that she’d written. I can’t remember how many instances were running but there was enough IO that the disk latency had gone through the roof to the point that simple queries were taking seconds to come back.

I’ve got a counter-story about braindead developers though on some bespoke insurance software we investigated. Turns out every time the users typed anything on any field on the screen, the client app did “select * from table where col like ‘%val%'” for some kind of autocomplete.
At the developers insistence, there were no indexes so it was a full table scan every keypress and some of the tables had hundreds of thousands of rows. 👌 Obviously it was fine with one user and their test data of about 100 rows, so it must be a problem with the customer’s database…🤦🏻‍♂️

@retro83 – see, never a developer’s fault…

I’ve got a supplier at the moment who insists the memory allowable to SQL is the default of 2147483647 MB. Despite there being 16GB on the server. You can provide all the proof Microsoft come up with, but they don’t believe it. Ditto defragging indexes on tables under 1000 pages.

The same supplier, after install, told me one database was scheduled to get to 18Tb within 5 years. The entire SAN – both DR and Live – was 14Tb.

@petec I make that > 2 petabytes of memory in a system with 16GB ram? That’s a hell of a lot of swap space 😛

Pro SQL Server Relational Database Design and Implementation

Easily worth the money.

“insists the memory allowable to SQL is the default of 2147483647 MB. Despite there being 16GB on the server.”

Do they also tell you to set the minimum memory to 16GB and add the SQL server account to the “Lock Pages in Memory” local security policy ;-P

I’m not sure what’s behind this statement “it is more about proving that the IVR system will work ok when being driven by SQL queries”
If the queries are about getting data in and out fairly simply then fine but please do not put application code in the database.
This relates somewhat to what 16stonepig says, much harder to maintain a dB than an application due to the dB containing state. It is also easier to scale applications than DBs (out rather than up) if you are CPU heavy.
Also, if you put the app code in the dB you may well end up locking yourself to SQL for a long time to come. Much better to think of dB as relatively dumb (from an application point of view) storage. Use the rdbms for what it is good at (managing data), not for what it isn’t.
You will find future migrations /adaptations to both fix design cockups and meet new requirements a lot easier if you do this.

Also how much data are we talking about needing to scale to here? The more data you have (particularly if it’s bound into one dB) the harder it all gets. You need to think about the fracture lines of the system (e.g. what needs to be consistent with what else, and what doesn’t?)

All imo of course. Someone will be along to tell me I’m wrong in a minute just like tends to happen in real work 👍