重组现有数据库？ [英] Restructuring an existing database?

问题描述

我不确定线程​​的标题是否相关，但我认为它解释了我的问题...这将是loooooong ...


基本上，我正在努力改变/改进现有的数据库，但是我被要求改变我认为需要对数据库进行整体重组的事情（说实话，关系和表格对我来说似乎有些奇怪/不必要）。我从来没有尝试改变数据库的结构，因此我需要帮助解决一大堆问题！


无论如何，数据库关系的结构如下：
1。有一个名为Person的表，其中包含每个客户信息（联系方式，推荐信息，案例状态等）。

这个表非常庞大，有很多字段和很多表链接在一起（更多内容在一秒钟内）。

2。还有第二个名为约会的表格，其中包含有关首次约会和后续约会的信息（两者的日期，结果和评论）。

这个表似乎应该分成两个表，因为字段在表中重复，看起来有点奇怪。

3。第三张名为Interventions的表格，我认为看起来还不错。

4。 最后，有很多不同的表似乎已经被创建来填充表单中的组合框选择 - 例如，有一个名为Gender的表（它只是性别类型的编号列表），一个名为Title（其中包含不同的标题，如Mr，Mrs等），一个叫Yes / No（!!!）等等。

其中大部分都链接到Person表（具有TitleID，GenderID等），因此当在表单上选择链接的组合框时，它们会更新Person表。这似乎有点冗长的方式，我想知道是否有一个更简单的解决方案。难道我不能使用字段/值列表吗？



这是第一个问题。我的下一个问题稍微复杂一些，需要一些解释：

如果客户脱离服务，然后 一年后，重新参与该服务，这个不会计入一个新条目（所以他们会保留他们的客户ID号）进入系统，但如果他们脱离服务然后，超过一年之后，重新参与该服务，将计入新系统（新客户ID号）添加到系统中（它有点傻但是它的工作方式，显然）。

无论如何，数据库目前无法执行此操作，并且一年内重新参与服务的客户将被添加为重新输入（这显然不是想要的）。我很确定这是因为每个客户端只有一组字段（我正在考虑与包含大部分信息的Person表有关，并将其拆分会使事情更容易编辑/修改但很可能错了！）


我一直试图找到解决这个问题的方法而不会打扰已经存在的东西（部分是因为经验不足）但是最好先咨询一些专家的意见而不是去做它而破坏某些东西。


我知道这可能不是足够的信息，但是这个帖子已经太长了所以如果你要我发布我讨论过的任何信息，请告诉我我会尽力提供它！


（公平地说，我认为发布数据库供人们查看可能更容易，但我不知道如何删除它包含的数据以及如何删除用虚拟数据或其他任何东西替换它......指针？！）

如果这还不够清楚，请提前感谢大家和道歉！

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!

推荐答案

数据库的结构可能不是最佳的，但如果有效，我建议不要管它。对结构的改变可能会破坏你的形式，而且可能不值得。这个问题可以通过决定你是否有时间和/或愿意重新开始来回答。现有模型会加速设计阶段，如果你能编写一些代码，转换记录并不难。


对于你的其他问题，你是正确的，因为有没有足够的信息。这只是我们无法了解的事情，我们可以理解与您的数据库相关的术语。诸如脱离，重新接合，将计数，将不计数之类的事情。数据会发生什么变化，以及数据需要以什么方式发生才能以您想要的方式工作？

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?

好的，请注意第一部分。我认为这是一个更麻烦的事情，而不是过分改变它的价值。我不确定我有多少时间重新开始（我只是在试探，你看！）


我会尝试解释第二部分更好......

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...

如果客户脱离服务，然后在一年之后重新参与该服务，这将不算作新条目（所以他们会保留他们的客户ID号码）进入系统

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

这基本上意味着他们要求数据库允许他们添加/编辑约会和类似于存储在数据库中的现有客户端详细信息，而不是向其添加新条目（这意味着将从自动编号分配新ID）。但问题是，只有一个字段可以输入第一个约会信息，所以它目前无法做到这一点！试图弄清楚如何做到这一点，然后在表格上显示它让我感到困惑。

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.

如果他们脱离服务然后，通过一年后，重新参与该服务，它将被视为添加到系统的新条目（新客户ID号）。

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.

这基本涵盖了。我只是为了一点背景添加了这个。数据库已经可以执行此操作，因为数据库已经有重新输入（一些有效，有些没有）


我认为混淆是我把数据库称为系统，也许？ br />

我不确定我能解释得多好，真的。也许明天我会有一个清醒的头脑。

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.

在尝试重组数据库之前，你花了很多时间写出来思考它是很棒的。


似乎问题与第一个约会字段有关。我们现在需要知道的是：这个领域的目的是什么？它究竟意味着什么？如果客户已离开X时间，他们是否应该保留其客户ID但是获得第二次第一次约会？这有任何意义吗？ X有多长？等等。这可能非常简单或非常复杂。


系统需要有规则来清楚地涵盖所有情况。理想情况下，您会获得规定规则的要求。实际上，您可能必须自己弄清楚要求。无论你做什么，都要确保你的要求得到很好的定义，这样你以后就不会像查看这个数据一样看新数据库了。

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.

这篇关于重组现有数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！