SQL database build time

ECO_Mental

Hi,

I'm not a software programmer by any stretch of the imagination but can ye guys help me with a work issue and fight that I'm about to embark on:rolleyes: I looking for typically how long would it take to build a SQL database and populate etc Obviously this is how long is a piece of string and I will try my best to fill in some of the details.

I am in the process of building a SQL database for a group public sector organizations. This Database is about 300,000 point location data all over the country with about 30-40 attributes associated with each location. The data is currently stored in separate SQL databases and we are joining into one large database to report nationally etc. The data is there just to merge into this new SQL database.

We have done the conceptual data model and produced a logical data model with all the tables and look up values etc so that work is done. So its actually to build the thing and populate etc.

Problem is its the public service and the way this org works is all it development is done through a developer that's on retainer (daily rate). Now when we were doing the CDM and LDM I got an other independent developer to do the workshop and produce the models. I asked him then to quote what would it cost to actually build it and test load up a data set, do some management queries and test the mapping displays. He said that would take him 9 days...no probs

BUT I was forced to use the dev that was on retainer and low and behold it has taken him 105 days and over 15 months to get to get this done. Dont mind the 15 months thats another issue but 105 f**king days to build a SQL database when he already had the LDM??? Im going to cause trouble about this tomorrow but something sounds fishy to me. Or does that sound like a reasonable amount of time???

beauf

Did you not have regular progress reports. What happened after 9 days passed and they were meant to be finished?

Without knowing the details. The database side of it is like asking how long is a piece of string. But from a project management side I wondering why is that missing...

ECO_Mental

This is the problem I wasn't directly managing it even thought I was reporting on progress to other groups and they never gave any timeline or estimate (crazy I know). (the 9 days was from an independent contractor different to the ones that actually did it.)

My query is probably too difficult for ye guys to give an estimate without seeing the data etc I know. But I was hoping that I might get an idea.

Listen its OK guys, I have the 9 day estimate from my original developer who did the LDM and he would have had a good idea of the work involved. I am going to stir the S**t tomorrow

beauf

I've started database jobs got maybe half a day into before realising the data is so bad that is a far more massive job than I thought and just realised it's beyond my resources and back tracked out if it. Maybe taken a different business decision etc.

That said I know people who don't know when to stop until they are really in trouble. I've seen projects go into millions and years because no one will stop it.

Seems to me you (or someone) are too arms length from this project. Where is the project/product owner.

I should add I'm not making a judgement call. These things are rarely simple. Its just what stands out to me. I am no expert.

BrokenArrows

If all the data is available and everything is planned. And its just a matter of building the database and writing some software to import the data then even 9 days might be a bit long.

105 days seems massively excessive.

You should be looking for a detailed explanation as to why it took so long.

ECO_Mental

BrokenArrows wrote: »

If all the data is available and everything is planned. And its just a matter of building the database and writing some software to import the data then even 9 days might be a bit long.

105 days seems massively excessive.

You should be looking for a detailed explanation as to why it took so long.

Well that is what Im planning to do today, it a bit complicated politically the set up we have and there is a bit of a cosy cartel going on between my senior management and with the developers that are on retainer. about a year ago I tried to set up weekly progress meeting set deadlines etc and I was told thats not how they do things here???


As I said I had everything for them the logical data model with all the tables, relationships, look up values etc etc all they had to do was programme it. F**k I could have done a a level 6 SQL certificate qualify and still have time to do it myself if I knew how long these guys would have taken....

BrokenArrows

And thats where the tax money goes.

beauf

BrokenArrows wrote: »

And thats where the tax money goes.

Outsourcing not living up to all the hype eh?

beauf

ECO_Mental wrote: »

W... there is a bit of a cosy cartel going on between my senior management and with the developers that are on retainer. about a year ago I tried to set up weekly progress meeting set deadlines etc and I was told thats not how they do things here???..

Sounds unlikely to have any authority to change culture in that case.

I'm not sure if you are public or private. But if I was you I'd be looking at what a good outcome for you here is. Whats a bad outcome.

sheroman01

ECO_Mental wrote: »

I tried to set up weekly progress meeting set deadlines etc and I was told thats not how they do things here???

The public sector summed up in one sentence.

beauf

This seems to be a multi owner project with project management mixed between public and private and development done by private. The delay seems to be in the private sector milking it and the public AND private sections not keeping a tight rein on it.

Designed and managed by committee projects often end up like this. Its why so many avoid it. Its much easier if you have a single owner who can dictate everything.

ECO_Mental

beauf wrote: »

This seems to be a multi owner project with project management mixed between public and private and development done by private. The delay seems to be in the private sector milking it and the public AND private sections not keeping a tight rein on it.

Designed and managed by committee projects often end up like this. Its why so many avoid it. Its much easier if you have a single owner who can dictate everything.

This is exactly the case...I just am looking for a bit of backup before I go into a meeting next week where I start hanging people!!!



On further reflection in getting my case together I remembered that when my original developer put together the CDM & LDM in his report detailing everything he did mention that he could write a script to build the SQL DB. It would have taken him two days to write this script but our gang declined because they were already working on it?? yeah a 100 days

colm_c

Is it actually just the SQL database, or also all the other bits that will be needed?

- Frontend for reporting
- REST APIs to query the data
- stored procedures
- ETL pipelines to ingest data when it's updated
- backups
- redundancy
- normalization / denormalization of data
- dedupe
- business processes / policies
- MDM
- documentation of all of the above
- etc

I suspect it's more than just a database, given the 100 days.

Some or all of the above would take 100 days or more.

You'll need the statement of work that the development team has got in order to understand what the scope is.

listermint

What does the migration process look like for the separated data sets.

Where's it being hosted cloud or on premise. Does it require redundancy.

ECO_Mental

colm_c wrote: »

Is it actually just the SQL database, or also all the other bits that will be needed?

- Frontend for reporting
- REST APIs to query the data
- stored procedures
- ETL pipelines to ingest data when it's updated
- backups
- redundancy
- normalization / denormalization of data
- dedupe
- business processes / policies
- MDM
- documentation of all of the above
- etc

I suspect it's more than just a database, given the 100 days.

Some or all of the above would take 100 days or more.

You'll need the statement of work that the development team has got in order to understand what the scope is.

None of the above :rolleyes::rolleyes:

(even though I dont know what half that stuff is:o)

This is a simple database of pieces of equipment at locations around the country with about 30 attributes associated with them. The only font end is them plotted on a OSI map. No search function, no query functions no API's. no documentation

The only thing I have is a pop dialogue box that when I click on an item the attributes are shown. Love to show it to you guys but Im too embarrassed for the country:o

ECO_Mental

listermint wrote: »

What does the migration process look like for the separated data sets.

Where's it being hosted cloud or on premise. Does it require redundancy.

The migration of the data was done just through excel, I did all the matching to the table and look up values. I also did the standardization of the data for the importer.


Its presently being hosted by the developer but it will be transferred to a public sector server. Not in the cloud



Meeting tomorrow where they are showing another database that they have been working on for a few years and I'm going to embarrass them by asking to demonstrate the functionality on what they have done for my project:p should be fun and quick

CalamariFritti

Was it just building the database - as in the sheer model with no data in it - or did it also include the import of the data from the separate databases?

If it includes data import with translation from multiple sources this could well be a reasonable number.

Snotty

Not defending the developer, but I've had many projects where the objective could be compressed into a single line, but the development has taken up a huge amount of time. I'm private sector and application owners and PMs love to say things like "sure its only a bit of code" or "it's only a small change" without understanding what actually is involved in the project.
But in this case, it does sound like a piss take.

colm_c

ECO_Mental wrote: »

None of the above :rolleyes::rolleyes:

(even though I dont know what half that stuff is:o)

This is a simple database of pieces of equipment at locations around the country with about 30 attributes associated with them. The only font end is them plotted on a OSI map. No search function, no query functions no API's. no documentation

The only thing I have is a pop dialogue box that when I click on an item the attributes are shown. Love to show it to you guys but Im too embarrassed for the country:o

You are contradicting yourself there.

To get data into any mapping system format, you need some way to expose that data, i.e. an API.

Not saying it's 100 days of work, but there is more than lashing it together into a database.

WhiteMemento9

ECO_Mental wrote: »

None of the above :rolleyes::rolleyes:

(even though I dont know what half that stuff is:o)

This is a simple database of pieces of equipment at locations around the country with about 30 attributes associated with them. The only font end is them plotted on a OSI map. No search function, no query functions no API's. no documentation

The only thing I have is a pop dialogue box that when I click on an item the attributes are shown. Love to show it to you guys but Im too embarrassed for the country:o

How do you think the information is being displayed without querying the database?

Sonny noggs

OP, I suggest you proceed with caution here, you may end up making a fool of yourself if you are. not careful.

ECO_Mental

Sonny noggs wrote: »

OP, I suggest you proceed with caution here, you may end up making a fool of yourself if you are. not careful.

From the first post I did mention that I was no way a software programmer and that is why I asking ye guys for some advice..........

Listen I think forget about it, its impossible for you guys to say how long is a piece of string without seeing the exact data, details etc also I'm starting to feel a bit of heat from you guys.

I am not going to go in strong on this anymore just going to ask a question, I have already in an email so my concerns are noted. I'm not signing off or approving any invoice my senior manager is so its on his head. If hes happy who cares really.

But after 18 months and 100+ days development when I originally got a quote from an independent dev saying it would take him 9 days???? I had to ask a question.

ECO out:P

beauf

There just seems to be lack of the basic project management activities on this project.

FishOnABike

Are you comparing apples with apples? 9 days might have been an estimate to go from the CDM and LDM to creating an empty database, importing some ideal test data for some basic sanity testing.

The specification for the finished product is surely much broader in scope.

Over 300,000 items of data with 30 to 40 attributes each have to be imported and merged from a number of different sources which may have to be sanitised and munged into the new database. This can be simple or an absolute nightmare depending on the consistency and accuracy of the existing data sets.

A database is useless without some way of accessing the data so it's reasonable to assume there has to be means to bulk import data, enter new data, edit existing data, and view data - as you indicated there is a map view interface to view attributes about a data point. Unless all interaction is via direct database queries logged in as a database user there must be some API.

Are there any report views on the data required? Is the database accessed from multiple locations? What sort of front end does it have - web based or application based? Are there any data protection issues involved that might require logging any access or transactions or influence the architecture? What user access controls are needed?

There's a large number of questions which would need the full project specification and possibly request for quotation, project bid and project schedule information to answer.

Without having all the information I'd tread lightly.

StupidLikeAFox

Yeah, rather than going in all guns blazing you need to draw a line in the sand and re-plan the project. You need to define the actual outcome, find out what work has been done, find out what work is outstanding and put a plan in place (with agreed timelines and milestones) for completing the remaining tasks. Then you need to track to this plan.

Going in to a meeting with a plan to embarrass somebody or show them up is petty and unprofessional. Mistakes were made on both sides here, so you just need to re-focus now and get the job done in an orderly fashion

rd1izb7lvpuksx

ECO_Mental wrote: »

On further reflection in getting my case together I remembered that when my original developer put together the CDM & LDM in his report detailing everything he did mention that he could write a script to build the SQL DB. It would have taken him two days to write this script but our gang declined because they were already working on it?? yeah a 100 days

I wouldn't put much weight in a developer's estimate - I've heard so many developers tell me they could do a job in a weekend where I know they were off by orders of magnitude.

I've also learnt the hard way to be careful about accusing other devs of milking contracts - sometimes a situation that externally seems ludicrous can become very reasonable when you talk to the developer or team, and learn the challenges they face. Data models are all well and good, but merging databases in one of those jobs that can have hidden complexity everywhere.