asked on

program implementation question

I have a windows app hitting a sql server database. The app allows for inviting customers to events (Golf,Racing,etc). I need to add up to 10 activities for the spouses to attend. In the database I have added columns Act1,Act2,etc. In a form I have a listview that allows for adding and deleting activities.

The problem: Let's say I add 8 activities. I decide later to delete the first 4 and add 3 more. What code would allow me to determine where to insert the new activities.

After deleting the first 4 that left data in fields Act5,Act6,Act7,Act8. I need code (vb6) that will help me determine where to insert the next activity. Ideally I could move the existing activities up as one above it is deleted. That way they would show up in the proper order in the listview.

Hope this makes sense.

Éric Moreau

>>In the database I have added columns Act1,Act2,etc.

You should better add multiple lines. For example, instead of having:

Spouse1, Shopping, Beach, Tennis

You should have:

Spouse1, Shopping
Spouse1, Beach
Spouse1, Tennis

This way you won't have problems deleting activities.

Also, what if you need to add 10 more activities?

Anthony Perkins

It is a bad idea to add the columns Act1, Act2, etc. to a table for each activity, because the database is no longer normalized and for all the reasons you mention. Instead consider creating a table of Activities and another table that is a cross reference of Customers to Activities. You will find this structure while more complex, far easier to maintain.

Also, please do not ignore the EE Moderators and maintain the following open question:
sql server performance Date: 03/28/2001 08:51AM PST
https://www.experts-exchange.com/questions/20098219/sql-server-performance.html

Thanks,
Anthony

ASKER CERTIFIED SOLUTION

Alon Hirsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rutledgj

ASKER

These activities are actually in a linking table that links the eventID with the customerID. I don't see a benefit of moving them to a separate table.

rutledgj

ASKER

Probably not the most efficient but it works.