Is it a good practice to use different table for different (but similar) business program?

Question

There's a very similar question: Modeling products with vastly different sets of needed-to-know information and linking them to lineitems? But I can't find an answer that help me;

Someone at the above Q&A points to designing database to hold different metadata information , which has a fantastic accepted answer, but since search function is explicitly needed in my program, I don't want performance to be compromised.

---

I'm a "technician" that uses PHP + Oracle to keep track of the selling progress of our company and generate reports. Our workflow generally looks like this:

1. Marketing guys provides prepared data-set to my system;
2. Frontline staffs (sales) mark progress on my system;
   • Anyone can search results in the system;
3. I generate reports back to marketing guys.

The problem:

Many columns of data-sets are the same (or can be considered the same), like these:

account|customer_name|gender|location|program_segment|...

But the marketing dept. like coming up new ideas (and abandoning existing ones), so each "sales program (campaign)" may has its own data, e.g.

For program 1, they may contain:

...|prev_coupon_code|last_usage_amount|...

For program 2, however, they may contain:

...|is_in_plan_1|is_in_plan_2|...

You got the idea.

Unsuccessful attempts:

• In order to hold all data, I used to use a "long enough" table that has all possible properties (columns), and leave blank/unnecessary properties NULL.

  But now I feel that it will never be "long enough", as there're too many "properties" and even more "sales focusing point": I drafted a 41 column table for a new version of the system and suddenly they proposed a new program that has information that can't fit.

• Someone suggested me to create "dummy columns" in the table and "remember" different meaning of them in frontend. This can work for several datatypes, like NUMBER(1) for Y/N, DATE, etc., but when talking about VARCHAR2, I'm not sure how many of that is enough...plus this makes the table look "dirty".

Question:

Frustrated, I'm now seriously considering using different tables for different programs, and use UNION clause to generate big report in case they're asked "how are we selling this month/season/year?"

Technically, is this a good practice? Should I implement it?

---

Edit #1:

To clarify, one "sales program" will generally be running for a few months before it got abandoned, and there'll be at least one data-set per month for each running program.

And there can be more than one program running at the same time.

Edit #2:

Those "program-specified" columns are of various number: one program may need 10, while another may only need 1.

Answer 1

This is one of those situations where there is no right answer, just a choice of kludges.

I would plump for using an XMLType to hold the transient data structures. XML gives us the ability to have defined schemas for each plan, but using an XMLType obviates the need to change the database itself. We can index XPath queries so the performance can still be good. Find out more.

The one problem is that writing queries against XML is a bit of a pfaff, but I think awkward queries will be an issue for whichever apporach you take.

Answer 2

You may or may not be aware that it is possible to index the contents of a character LOB in Oracle. You might look up Oracle Intermedia / multimedia (depends on your version) and talk to your DBAs to see if it is available to you.

This would make it possible to create a common structure for common data items - eg campaign, start_date, end_date, &c but then to dump your spreadsheet/xml data/csv file into a CLOB field.

The plain-text indexing is not as hard as it first sounds and it is very cute indeed.