dual purpose tables

BEFOR

On a table that receives the same types of values for two potentially different user types. ("potentially" since there are two types of "plans" users may sign up for; they can choose either one or both of the two plans and users may also upgrade from one plan to the next) Here I am asking about values like start and end dates; same column value types and format, which are duplicated on the table.

Would it be best to combine the both of the tables into one -- in which case many of the row values will end up being NULL. Or would it be better to halve the table?

You see, after a users initial registration their temporary values already need to be echoed onto over a dozen seperate tables one by one and I'm trying to streamline the process. I understand transactions could help automate but also that there could be complications with transactions.

nemonoman

Absolutely use one table with a 'member-type' column. You will be sorry in the long run with a two-table approach.

BEFOR

thanks nemonoman,

can I ask why you say this? by a "member type" column I assume you mean a col to identify which "plan" they go with (either one, the other or both, ie, "1","2" or "3")

I'm wondering about my other tables, like "contacts" with members contact info (phone/fax, address, etc) again two tables with one for each "type" -- but here the values may be slightly different. One contact table is for the contact listed in the public view, onsite, (to be found by site browsers) and the other is so I can contact them directly (with potential business).

Too they could use seperate contacts for tracking purposes.

Any thoughts on how to treat this?

nemonoman

The whole idea of a database, particularly a relational database, is to think about entities (person, address) as elements with multiple attributes (member type, address type, etc.) These entities have relationships.

Making a person an entitiy with a membertype flag means that you merely update a single record rather than duplicate and or delete a whole record when his status changes.

Putting all the items in one table means you can more easily search and report.

SELECT id, memberstatus FROM person WHERE lastname='smith'

One search. You don't need to look through 2 or more tables trying to find the right guy based on his member status.

Reporting:
SELECT memberstatus, count(*) FROM person GROUP BY memberstatus

would show how many records of each type you have. You could add:

SELECT memberstatus, sex, count(*) FROM person GROUP BY memberstatus, sex

and get a report showing numbers of m/f of each member status

etc.

Read up on database design. It isn't hard to design well -- and your questions show a fundamental ignorance that will end up biting you in the ass as you try to grow this system.

BEFOR

Putting all the items in one table means you can more easily search and report.

True, but what I'm weighing is if this should be done due to the large percentage of NULL values that will result. Of course it may require a join to do searches. - But it would much simplify.

For the contacts, same thing. However if the table is combined and I'm not disputing you, there will be 40+ columns. There is the very real chance I am giving users too many options (ex: too many contacts; each member can have 3 seperate company contacts; seperate names/phone#'s/fax#'s/email addresses/physical addresses, etc)

nemonoman

You need to use related tables. Study some basic database design.

BEFOR

I've referenced some key basics (php manuals) and some very basic table relationships (T. Muck) and K. Yanks' "Database Driven Website" which wasn't all that helpful along with just now O'Reilly's Web Database Applications with PHP and MySQL (entity relationship).

-Am waiting on "Practical Issues in Database Management: A Reference for the Thinking Practitioner" by Pascal

You know if it?

One thing that isn't coming out in my posts is that I have more of a complicated structure (my opinion). I'll admit I don't have the basics down pat yet but my registrants have three main identity types (one type, another type or being of both types) and registrants can change types at any time. Table attributes can vary by these types.

For the admin. panel, I don't have the code to do Inserts across multiple tables at once/transactions so I'm taking this into account to keep tables below a certain # to process new registrants.

I would not simply post as a way to sidestep effort on my part.

Some of the posts I've made have not gone into enough detail due to the facets mentioned above.

One factor I haven't much considered is reporting, which is a huge factor with this new site. Especially with the PPC campaign.

Practical example: Runtimes for two types of registants:
Linking to single Runtime table for reporting/other reasons that contains both sets of runtimes.

A) in this case, to pull these values will require a two way join.
Add one more linked table (add another factor to integrate into reporting) and it is a two way join.

😎 now that same runtime table is instead split to contain only two key links (one for each runtime for each of the two types) I'm into a three way join just to get the runtimes -- what happens when other factors are involved other than just runtimes?
C)which is I why I am inclined toward a single runtime table containing both runtimes (yes, some values will be NULL or 0000-00-00 by default)

I've not seen practical table linking theory presented in this way. like "C" which was forum-recommended.

nemonoman

OK I acknowledge that you are not an idiot, so I'll temper my suggestion. You need to talk to a database geek. I don't know where you personally will find one, but that's what you need to do. In a coffee shop with lots of blank sheets of paper and doses of caffiene. You clearly need some realtime design feedback. Worse than knowing nothing is knowing a little but not enough. Call around to larger corporations in your area, talk to the VP of Information Services, and find the brightest DB geek they have and suggest some free coffee.