Create Full Membership Profile?

PHP
1

Hi,

I am currently developing a system which enables people to create a profile by entering their name, email address and password. They will then receive a confirmation email. Once they click their confirmation email they will then be taken to a screen to enter details for a wide range of 15 different fields to create a full, detailed business profile.

However I am unsure on the best way to go around it. Should I use to two seperate tables or keep it all combined in one. It would be much easier to use one table but Im not sure if this is technically the best thing to do.

Does anyone have any views on this please?

2

What benefit do you foresee by having two tables?

3

None, thats why I want to keep it to one table.

4

I think there are occasions when splitting tables can lead to benefits (search for [google]vertical partitioning sql tables[/google]) generally for reasons of optimization.

In this case you seem not to be doing it for reasons of optimization, so you might be spiralling into the trap of [google]premature optimization[/google].

Speaking as a PO repeat offender I can only say, it was a fair cop. Guilty as charged.

5

I think the premature optimisation thing is largely a myth. You should plan for the future. It’s a natural part of good design.

6

To really answer this question you need to know how much data you plan to store in regard to the profiles. What columns do you need, what is the length of each column? What is the total bytes per row? How many rows do you anticipate?

Once you have those answers you can then see how paging may affect your table. Will your rows exceed the length of a page and thus require splitting into two pages which will cause a slower lookup?
Then split it into two tables, you will gain a little bit here. However, split it in a smart fashion. If there is some data that you will not query very often, move that data to a separate table and only query it when you need it. Otherwise you really don’t reap the benefits.

You can easily do this after you are setup using 1 table. I’ve done it numerous times in Production environments. After hitting millions of records we were seeing a performance hit due to the records being split across pages. So we moved the very large data (blobs, text, etc) to its own table. Then later on, we still were getting some records splitting across pages so we moved data we didn’t access very often into its’ own table.

In the end we had 3 tables containing the data and our performance was speedy again. It was then we quickly learned if we had a rough idea how much data would be stored in each column and how often we needed to access each column we could have avoided all of that in the beginning. Did it hurt to learn it later? No. Maybe you don’t really know how your users will use some of your columns, or how often they will be used, so learning it later isn’t going to hurt.

7

Premature optimization lies at one end of an extreme whose opposite end is no plan at all.

Maybe it is a myth for you, but it is not for me.

8

Yeah, if you’re talking about an extreme case, perhaps. But I think looking at a relatively simple database structure and considering breaking it into smaller tables to make your life easier in the long run is an example of a fairly simple optimisation that should be considered upfront. I’d hate to never plan anything for the future and end up with a system that was extremelly difficult to change as a result. I tend to plan for change and optimise early, but not to the point of “analysis paralysis” or anything like that.

I’m currently working on a system that will track online games for a company. The company has customers that themselves have games attached, and it all needs to be done as a cms. I say “customers”, but right now the plan is that there will only be tracking for one customer (the customer can have thousands of games and everything needs to be logged and tracked on our end). I’m simplifying this because I can’t give out much real world information on it. The point I’m making here though is that I’ve already designed it so that we could be tracking games for multiple different companies, even though this was never part of the requirement spec. If in a year’s time we sign a contract with another company and need to monitor their games, we can now do it relatively easily. This added maybe a week’s worth of development time for me and is not something that we will be using from the start. The thing is though, if I hadn’t done this and we needed this functionality in a year, it would have been much more difficult to change. So I took the spec and planned ahead for the future, even though this wasn’t part of the requirements and I was not asked to do it. I think this is actually a good thing to do. That’s an example of early optimisation that I would not consider premature.

9

Thanks, I’m glad you replied @aaarrrggh; and I agree with your assessment, naturally.

I seems to have been a long time since Premature Optimization (PO) has been mentioned when it used to be a frequent criticism flung about between the power users that used to inhabit the now defunct PHP Application Design (PAD) forum.

By nature I am an extremely cautious developer, and fully recognise and appreciate everything you have said in your reply.

I think perhaps the most important thing about PO is that exists as a moniker, a label if you like, because like many I stop and think about how a system might evolve in the future and try to make sure the door is left open for extension etc without having to re-write the whole thing.

Now, my as brain knows the phenomenon of PO exists – that you can over-engineer a solution – I can say to myself, “hang on are we sure this is not going down the road of premature optimization here?”.

Then I can mentally double back, go back to the spec, ask more questions of the decision makers etc and see if I can justify the extra planning I seem to be doing.

The counter to PO often seemed to end up summarized in language such as “just do the simplest thing which works, and if that causes your code to run slowly with bottlenecks and so on, then refactor.”. That may seem glib, but can be true some times.

The problem is as a new developer you don’t have the confidence to know whether you will be able to refactor your code later and you may fall into the “analysis paralysis” loop of constantly thinking about your code, but being unable to decide what to write for fear of failing. Again, I have been guilty as charged in the past.

In trying to find a link for the OP I stumbled across the dual monikers of Vertical Partitioning and Horizontal Partitioning of tables. I knew about Vertical Partitioning but did not know it had a name, and had never really thought of using Horizontal Partitioning, ever (that I can remember). Now I have an extra tool in my arsenal, and it has a shorthand, a name.

So, I learned something new.

Good thread, and a good opening question @justlukeyou;. Sorry if the talk of PO has dragged it a bit off-topic.

10

Another technique that is really good for this is using Agile practices, having “Three amigos meetings” or just pair-programming can help tremendously in this scenario.

:weee: Wohoo! and good reminder for me to brush up on my moniker names. :slight_smile:

On the contrary, I think this is the exact direction this discussion needed to go. All of these tie back to the OP questions and that is what I enjoy about SitePoint. You usually don’t get just an answer, you get a discussion on the topic at hand, things you may not have considered that end up being vitally important.

11

i think u can create two table and these r PARMANENT table and TEMPORARY table so when user register all data will be saved in temporary table after that when hr varify his/her account by email then all data trasfa from TEMPORARY to PARMANENT and temporary data will be deleted :slight_smile: After that he can update his profile :slight_smile:

12

Hi,
I am using the same system than you in my strollers website.
I’m no expert on the subject, but someone a little more prepared than I recommended me to use two seperate tables. I think this is better option for optimizing the website.

13

But I think that if you are doing for the first time then its look also matters. I think you won’t want your visitors to get confused finding stuffs.