Restructuring an existing database?

[hedges98]

I'm not sure if the title of the thread is relevant but I think it explains my problem... This is going to be loooooong...

Basically, I am working on altering/improving an existing database but have been asked to change something which I think requires a whole restructuring of the database (to be honest, the relationships and tables seem a bit weird/needless to me). I've never tried altering the structure of a database before so have a whole host of questions I need help with!

Anyway, the database relationships are structured as follows:
1. There is a table called Person which contains each clients information (contact details, referral info, case status and so on).
This table is massive and has a lot of fields and a lot of tables linked to it (more on that in a second).

2. There is a second table called Appointments which holds information on first appointments and subsequent appointments (date, outcome and comments for both).
This table seems like it should be split into two tables as the fields are repeated in the table and it looks a bit odd.

3. There is a third table called Interventions with various information which I think looks okay.

4. Finally, there are lots of different tables which seem to have been created to fill combo box selections in forms - for example there is a table called Gender (which is just a numbered list of gender types), one called Title (which contains different titles such as Mr, Mrs etc), one called Yes/No(!!!) and so on.
The majority of these are all linked to the Person table (which has TitleID, GenderID etc) so when the linked combo boxes are chosen on forms, they update the Person table. It seems like a bit of a long-winded way and I was wondering if there was a simpler solution. Couldn't I use Field/Value Lists instead or something?


That's the first question. My next question is slightly more complicated and will need some explaining:
If a client disengages from the service and then, under a year later, re-engages with the service, this will not count as a new entry (so they'll keep their client ID number) into the system but if they disengage from the service and then, over a year later, re-engages with the service, it will count as a new entry (new client ID number) being added to the system (it's a bit silly but it's the way it works, apparently).
Anyway, the database is currently incapable of doing this at the moment and clients re-engaging with the service under a year are being added as re-entries (which is obviously not what is wanted). I'm pretty sure this is because there are only one set of fields for each client (I was thinking something to do with the Person table containing most of the information and splitting it up would make things easier to edit/modify but am probably wrong!)

I've been trying to come up with ways to solve this without disturbing what is already there (partly through inexperience) but thought it best to ask advice of some experts rather than going at it and breaking something.

I'm aware this is probably not enough information but this post is already too long so if you require me to post any of the information I've discussed then let me know and I'll try and provide it!

(To be fair, I think it might be easier to just post the database for people to look at but I don't know how to remove the data it contains and how to replace it with dummy data or whatever... pointers?!)

Thanks in advance guys and apologies if this isn't clear enough!

[ChipR]

The structure of the database may not be optimal, but I would recommend leaving it alone if it works. Changes to the structure would probably wreck your forms, and it's probably not worth it. This question can be answered by deciding whether you have the time and/or desire to start over. The design phase would be accelerated by the existing model, and converting records isn't hard if you can write some code.

For you other question, you are correct in that there is not enough information. This is just a matter of putting things we don't know about in terms we can understand related to your database. Things like "disengages ", "reengages" , "will count", "will not count". What happens to the data, and what needs to happen to the data for this to work the way you want?

[hedges98]

Okay, point noted about the first part. I was under the assumption it was more hassle than it's worth to change it too much. I'm unsure how much time I have in regards to starting again (I'm only temping, y'see!)

I'll try explain the 2nd part better...

If a client disengages from the service and then, under a year later, re-engages with the service, this will not count as a new entry (so they'll keep their client ID number) into the system

This basically means that they require the database to allow them to add/edit details about appointments and suchlike to the existing clients details stored on the database and not adding a new entry to it (which would mean a new ID would be assigned from the autonumber). The problem with this, though, is that there is only 1 field to enter 1st Appointment information so it currently can't do this! Trying to work out how to do this and then show it on a form is confusing me.

if they disengage from the service and then, over a year later, re-engages with the service, it will count as a new entry (new client ID number) being added to the system.

And this is covered, basically. I was just adding this for a bit of background. The database can do this already as the database already has re-entries (some valid and some not)

I think the confusion was with me referring to the database as system, maybe?

I'm not sure how better I can explain it, really. Maybe with a clear head tomorrow I'll be able to.

[ChipR]

It's great that you have taken the time to write this out and think about it before trying to restructure the database.

Seems that the problem is related to the 1st Appointment field. What we need to know now is: What is the purpose of this field? What does it actually mean? If a client has been away for X amount of time, should they retain their client ID but get a second 1st Appointment? Does that make any sense? How long is X? And so on. This could be extremely simple or extremely complicated.

The system needs to have rules to cover all the cases clearly. Ideally, you are given requirements stating the rules. In practive, you may have to figure out the requirements yourself. Whatever you do, make sure your requirements are well defined, so that later you won't be looking at your new database the same way you are looking at this one.

[hedges98]

Seems that the problem is related to the 1st Appointment field. What we need to know now is: What is the purpose of this field? What does it actually mean? If a client has been away for X amount of time, should they retain their client ID but get a second 1st Appointment? Does that make any sense? How long is X? And so on. This could be extremely simple or extremely complicated.

Sorry if I haven't been clear enough but the 1st Appointment details are contained in three fields, not one. These are Date, Outcome and Comments.

I don't require the database to automatically calculate if the client needs to use an existing ID or is assigned a new one - this will be done by the staff (and will hopefully save me a headache)

Although, thinking about it, I think my problem is more complicated than I first thought:-
If a client does re-engage with the service within a year then they are going to need more than just a new Appointment section - the referral information, case status parts will also need to be new.

I'm having quite a lot of trouble explaining this well, I think. Would it be easier for me to attach the database (how would I do this without the data / dummy data) here?

[ChipR]

I wouldn't be able to download anything from here anyway, but the structure of the database wouldn't reveal anything new about your problem or how to deal with it. As you've found, it's complicated. It's complicated enough, in fact, that anyone who doesn't actually know and use the system will not be able to help you. Anything I suggest is as likely to be a problem later as a solution.

Don't be too disappointed, since this is what I've expected from the first post. The idea was to get you thinking through the requirements, and the need to determine them fully. If you can't explain it, at least on paper to yourself, you shouldn't be designing it (yet).

[hedges98]

The idea was to get you thinking through the requirements, and the need to determine them fully.

Therein lies my main problem. I didn't create this database and it's obviously quite clear that whoever did wasn't given a full list of the requirements - partly because I don't think the people that wanted the database knew what they wanted it to do from the beginning and have been coming to me with bits and pieces they want it to do if and when they think of it. Obviously not the best way to go about it, right?!

The thing is, I'm fairly certain the stuff that I need to add to it is achievable but describing it is a massive pain (it's in my head but getting it out is really long-winded as you've read).

Here's something that may help me in the right direction and maybe I can explain further from there... This is the Appointments table
Table Name=[tbl_Appointment]

[I]Field               Type      IndexInfo[/I]
App_ID           AutoNumber        PK
App_Per_ID       Number        
App_Date         Date/Time
App_Outcome_ID   Number        
App_Comments     Text
App_FirstDate     Date/Time
App_FirstComments Text
App_First_Outcome_ID Number

I don't know if you can imagine the data in it (or if there is a way I can post how it looks?) but the table contains a lot of blanks in the fields relating to the 1st Appointment information (as there is only one 1st Appointment for each client). The App_Per_ID is the client ID number by the way.
Is it possible to move the 1st Appointment fields (cut App_FirstDate, App_FirstCommen ts, App_First_Outco me_ID along with copying App_Per_ID) into a new table named 1st Appointments? Would the forms and reports linked to these fields automatically refresh and sort themselves or would this mess everything up?

[ChipR]

It is almost certain that any changes to tables are going to cause some forms and reports to break.

I can picture the data from the table structure. Well posted.

I'm not sure how this relates to returning clients. If there should be only one first appointment, then it is specific to the client. Therefore, I would put it in the client record. Maybe each client has a FirstAppointmen tID, FirstComments, FirstOutcomeID. If there would be multiple "first" appointments, there would be several options. Remember, this is just thinking out loud, since I have nowhere near enough information to know whether this would be "right".

[hedges98]

I think you're onto something there, you hero! Moving the 1st Appointment details to the client (well, Person) table would totally solve the issue of needing to have more than one first appointment if a client re-engages within a year (and uses the same ID number). Thinking about that, though, if a client did have multiple first appointments - wouldn't it create duplicate records of the client with the only difference being the 1st Appointment details? I'm aware you don't know what is contained in the Person table (there are a LOT of fields, 63 in fact!). Would posting the table structure be of any help?

Although now comes the headache of figuring out how to move it without upsetting what's there. Is my only option to go through the database manually, noting what forms/reports are linked to these fields and then changing them to the new ones?

Thanks for your help so far, I imagine it's quite difficult to follow these lengthy rambles!

[ChipR]

I don't know of any way to fix things after changing the table structure other than to go through everything manually. I'm going through this myself at the moment for a timekeeping and accounting system, and it is not fun at all.

As you noted, if there will be multiple "first" appointments (perhaps you can come up with a better term, since this defies logic and hurts my brain), they will need to be in a separate table. I don't think it's necessary to post the Person table structure right now.

[hedges98]

Hmm, this sucks. It looks like I'm going to have to move the 1st Appointment fields (cut App_FirstDate, App_FirstCommen ts, App_First_Outco me_ID along with copying App_Per_ID) into a new table named 1st Appointments and manually change what forms and reports it is linked to. Rubbish!

There will definitely be multiple 'first' appointments, which is where my problem is mainly. I know it doesn't logically make sense but it's the only way to explain it!

I'm beginning to think that creating a new database from scratch may be easier...