Re-purposing Databases

[SueBK]

I have a database that works well for a project that runs at a State level. I now have a new project with very similar data tracking requirements, but it is a shire (county/province) level.

The essential data is the same, but there are some obvious changes. For example, the original database tracks the Shire that work is conducted in; the Shire database will track against the region.

What is the best way to repurpose my database? I see three options:

1. Start from scratch. Use the original database as a reference only. Where feasible, copy forms/reports across and reset the properties for fields against my new tables. This seems to be the cleanest option, but the requiring the most time inputs.

2. Use the current database. Rename fields in tables; update as I hit them in the other elements. I see that this is the potentially the most frustrating.

3. Use the current database. Leave the field names alone; change the labels in the forms/reports to reflect the new data types. This strikes me as the quickest, but potentially the most dangerous option.

Comments?

 

[Simon_MT]

Historically you may not be able to get information at the Shire level but you can still get information at the State level. You probably need to distinguish information that does not have the Shire detail or see if you can generate the Shire in the old transactions.

Simon

 

[GlennOwns]

It sort of depends on how proficient you are in Access.. What used to take me 8 hours takes me about 20 minutes now... So back then, OF COURSE I would copy/paste!

But now, it's not worth the trouble since I can write it in the same time and grow a better understanding of how it's going to work.

 

[gemma-the-husky]

these decisions are never so easy.

in principle you can re-use stuff. in practice it is often not so straightforward. It will help that you are the designer of the previous app, as you will know exactly what needs changing.

another approach is to try and parameterise the single application, so that it will work in both cases. that way you have only a single databases to maintain, and any additional functionality becomes incorporated in both versions.

 

[SueBK]

Thanks for your input. I've gone with a straight copy; list of 're-purposed' fields; updated labels on forms and reports. Not the greatest solution, but the reality of my life is - I don't have time, I don't have inclination and the second project quite possibly doesn't have the volume of work to warrant a lot of time re-creating the database.
I realise, having used the database for a week or so, that re-writing from scratch with some slightly different principles in design, would have been by far the best option. So, live and learn.

 

[Lightwave]

That's usually what happens you realise design decisions made in the table definition limit applicability to other tasks.

 

[gemma-the-husky]

Thanks for your input. I've gone with a straight copy; list of 're-purposed' fields; updated labels on forms and reports. Not the greatest solution, but the reality of my life is - I don't have time, I don't have inclination and the second project quite possibly doesn't have the volume of work to warrant a lot of time re-creating the database.
I realise, having used the database for a week or so, that re-writing from scratch with some slightly different principles in design, would have been by far the best option. So, live and learn.

all this is also why, if you get a chance to look at stuff other devlopers have designed, you see a lot of "i wouldn;t have done it like this"

i am pretty sure they never start with a fully specified design brief - so the first shot is "about right" - but then over time extra stuff gets added. this has to be made to work with the existing, and ends up being achieved in a slightly different way, to what would have been done if it was part of a project from the start.

the more normalised the data is, though, the easier it is to add extra stuff.