HELP!! eliminating fields from a table

[ruba]

I have a table with 20 fields. I want to eliminate 8 of them and add instead two fields to hold the data of the eliminated fields.

Say my fields are:

FirstName, LastName, PhoneNumber, Department1, Address_Dept1, Department2, Address_Dept2, Department3, Address_Dept3…

I want to delete Department1, Address_Dept1, Department2, Address_Dept2…and instead add only two fields: Department and set it to be a dropdown where the options/values would be Department1, Department2, Department3… and an Address field to hold the address.

This way data entry person can choose Department from the dropdown and put the address in the Address field.

My problem is that I need to write a script to be able to move the data from the fields to be eliminated and add it to the two new fields and don't know how to or even if it's easy to be done.

I'm still new to this and any help would really be much appreciated!!

[mr_vodka]

Do any of the records have more than one field with data in it? IOW, does one record have data in more than one field of Dept1, Dept2? If so, which one do you want to keep or do you want to keep all of it?

If you want to keep all of it, you would do this by creating related tables to store the multiple addresses and departments for each contact. If there is only one entry for each record or you want to store only one, then you can write a one time script that with populate the 2 new fields with the data that you want.

[ruba]

I want to keep all the data. Just delete the multiple columns of departments and addresses and instead create 2 columns to put that data in.

Could that be done without creating multiple tables, i.e. in the same table?

[Fenton]

Mr_vodka is right.

Move the data into less fields, into multiple related records in another table.

Fields:

ParentID; Department; Address

The data could be moved by a Loop script, or you can just do an import for each Dept, ie., Dept1, then Dept2, ....

[David Jondreau]

There's usually not a good reason to keep information like this in the same table. Follow the suggestions here to split it up into multiple tables, it's good advice.

[ruba]

i do appreciate all the help/feedback.

the problem is that my client is totally against creating other tables.

they have one big flat table which is their db, the thing that's creating lots of trouble when connecting to my web application.

i tried to convince them to do normalization but they're not tech. people and they want just one table with everything in it!!!

thanks!

[David Jondreau]

The sooner you get the confidence and skills to convince your clients to accept what needs to get done, the better.

I don't mean to sound condescending, but I'll tell you from experience, you're the tech person (even if an FM novice), so you determine what's appropriate technology.

Tell them whatever they want with the Departments can't be done in one table.

[mr_vodka]

I would also demonstrate to them in terms and examples that they can understand why normalization is necessary. Show them the limitations of the flat file and why [color:red]NO successful business in their right minds would follow a flat file model. Explain to them how it limits their ability to grow.

Edited July 30, 2007 by Guest
word garbled up. :)

[ruba]

thanks a lot!

for a beginner in the field, any advice from experienced professionals is so valuable!

I'll work on all that....and hey...I'll keep you updated.

[Fenton]

I think mr_vodka meant "no" not "any successful business..."

We don't really know what your "web application" is, but one can understand that it is not designed to handle "Dept.1, Dept.2, ...Dept.n" It is not so much that the multiple Depts. cannot be "shown." It's that the data is then almost useless for anything else; difficult to work with at the very least.

If you really must keep them in the same table, then you could put the data in a couple of repeating fields (Dept., Address). Or you could leave their fields as is, and create a calculation in their table that collected the data with returns between the values. But I cannot see that either of those methods gets you very functional data.

I cannot imagine how they can do anything other than a simple, and rather clunky address book with only 1 table.

[mr_vodka]

Thanks Fenton. ;)

[ruba]

it's a data driven web and i'm using asp.net

i have many dropdowns, search textboxes.

one of those is a dropdown for the *department* followed by an *Address* textbox.

once you choose the department, you can search for the address in the search textbox.

the problem started when configuring the data source for the department dropdown and address textbox.

i didn't know what field i should link it to because department isn't one column you can search for values in that column; it's multiple columns and they want to search based on column headings.

so for example you choose department1 from the dropdown, then enter chicago in the address textbox and the search should retrieve all the fields:firstName, lastName...related to that department/address.

so i couldn't come up with an efficient query string that can solve that...

[Fenton]

Yep. You really need an proper relational design. But they don't want one. If they are unmovable then you might have to take their departments and post-process them. Create a calculation field in their file (can be unstored), to collect the Depts into 1 field, and their Dept_Addresses into another, separated with whatever your application will accept to get it in. Then do whatever you have to do to use it.

Another alternative is to use an XML Export, with an XSL stylesheet, to build their data into the form you could use. However this would likely require 2 exports, or the ability to import xml, because what you want is relational data, 2 tables, which is not going be easy to access from 1 file with a flat format like tab or csv, but is fairly easy to access with 2 xml imports from 1 xml file. So, either 2 exports of tab or csv, or 1 export of xml; 2 imports in either case.

Of course I know very little about SQL queries, so maybe you've got other tricks up your sleeve.