Why break tables up?

[Canadiangal]

I am redoing a very old access 95 database. They had one main table with all their information. I am trying to talk the owners of the database into breaking the table into logical tables. They said they would rather deal with large tables then relationships. Can anyone one give me any valuable reasons on why to break up tables? They are using forms to enter the data, so no one ever sees the main database. So far the only reasons I could think of was speed, consistency, and double sorts.

Thanks in advance

[June7]

Does the table involve a lot of data replication - such as employee or company names? How is the name data entered - by manual typing so there is not consistency in spelling? Should at least have lookup tables for employee and company names to maintain consistency. Are there are lot of empty fields? It is a balancing act between normalization and ease of data entry/output. However, if these owners are 'superusers' who like to work directly with tables then I suppose have to bend to their preferences.

Not sure what you mean by 'double sorts'.

[Canadiangal]

No, I can't see data repeated; however, since they are using this table to hold employee information and dates of last shots, and dates of certifications due, there are a lot of date fields. There are not a lot of empty fields either. Basically, one employee has a row of approximately 50 fields. I would think it would be hard to generate reports this way. For double sorts, I simply mean that data in one table can be sorted by employee name, where as in another table it could be sorted strictly by dates. For no other reason, I would think that breaking up the tables would allow the system to grow naturally. For instance, perhaps they would use the database in the future to order shots. Their would simply be no point adding that information to the one main table.

[June7]

Use queries to do sorting and filtering where more than one field is needed as criteria.

So there is a date field for each type of shot? True, this is not normalized data structure and reports would be awkward but might be workable. This structure certainly doesn't allow for historical data which is also fine if that is not important. If a new type of shot is required, would mean modifying table, query, form, report - assume this rarely happens.

[stmoong]

It sounds like the existing table stores 2 pieces of information - Employee info and Shots info. From user's point of view, it wouldn't matter how many tables there are behind the scene. However, it sounds like your users also wants to be able to maintain the database themselves? Or perhaps they are used to using MS Excel for data entry.

This would be more of a non-technical issue to be resolved. More of finding out what are the users' concerns - why they prefer one big table.

Previously, I have encountered a user who insisted that we enhanced an existing Access database, but we are only allowed to use macros and queries, no VBA! In the end, we managed to convinced her that we would use as much macros as possible and only use VBA for cases where macros are not able to do the things we want.

So, personally, I would accomodate the user unless there is a technical reason of not able to meet certain requirements. One reason I could think of is perhaps some reports could be generated faster if the tables are split.

Perhaps, you could provide the users a query to generate the same view as the one big table. Might be they are used to seeing everything in one view, like in MS Excel.

[Canadiangal]

They prefer the one big table because they do not want to have any relationships. Yes, they will be maintaining the database when it is completed. There are about 500 records in the table and the table has around 50 fields. So not unmanageable. I still don't like the thought of not having the data normalized.

Tina

It sounds like the existing table stores 2 pieces of information - Employee info and Shots info. From user's point of view, it wouldn't matter how many tables there are behind the scene. However, it sounds like your users also wants to be able to maintain the database themselves? Or perhaps they are used to using MS Excel for data entry.

This would be more of a non-technical issue to be resolved. More of finding out what are the users' concerns - why they prefer one big table.

Previously, I have encountered a user who insisted that we enhanced an existing Access database, but we are only allowed to use macros and queries, no VBA! In the end, we managed to convinced her that we would use as much macros as possible and only use VBA for cases where macros are not able to do the things we want.

So, personally, I would accomodate the user unless there is a technical reason of not able to meet certain requirements. One reason I could think of is perhaps some reports could be generated faster if the tables are split.

Perhaps, you could provide the users a query to generate the same view as the one big table. Might be they are used to seeing everything in one view, like in MS Excel.

[Rawb]

Relationships aren't something you maintain. Relationships help you lessen your database maintenance by improving data reliability and consistency - When you change one piece of data, everything related to it automatically "sees" the change. Proper relationships can also lessen the amount of data entry needed as well as file size (sometimes drastically) depending on the amount of duplicated data.

In addition to all of this, a properly normalized database makes it easier to expand. Adding new functions and capabilities requires less work, making updates and additions easier, faster, and (most importantly?) cheaper.

If they don't feel they need a normalized database, then maybe they should do away with it completely. Based on how they seem to use it, an Excel Spreadsheet would work just fine for them and would require less maintenance than an Access database.

[June7]

Review http://forums.aspfree.com/microsoft-...el-349267.html