| SitePoint Sponsor |
Hi,
im up with a new project in mind and i've been thinking about my database structure. So, for each user of my site i'll have to store:
- Configurations
- Profile
> Personal info
> Appearence info
> Interests
> Photos
- Messages (like an internal e-mail system)
basically this. so, what would be better? 1 table for each item, or 1 table for each item and subitem?
And just one more question. Can i store an array() on the a database field and then restore it on a php variable array?
Thank you
Hello,
you can use serialize() and unserialize() to store and restore an array (serialized array stored as text or varchar...)
greez Pozor
Although you can store arrays as mentioned, you probably don't want to.
This tutorial on db design is essential reading: http://www.oreilly.com/catalog/javad...apter/ch02.pdf
Hi...
Originally Posted by dreaz
If I was only allowed to give one piece of database project advice it would be this: Do the DB schema last.
It has the biggest impact and is the thing most likely to be wrong (being the greatest distance from the requirements). Start with some user interface code and fake the database whilst you work on it. You also end up with a cleaner simpler design this way, it's faster and less accident prone and you learn more because you have less to think about at each juncture.
If it's a practice project then just try it this way. You will be surprised the direction you end up taking.
yours, Marcus
Marcus Baker
Testing: SimpleTest, Cgreen, Fakemail
Other: Phemto dependency injector
Books: PHP in Action, 97 things
Interesting. I'm going to try that this week. I have a small project coming up where users read a chapter of a book, and then they check off when they're done. The database just keeps track of what chapters they've read and when they're done the application will tell them about it and present some kind of silly reward page. The site will also hadle basic login and editing of account information. I was almost ready to start the design of the database. But I'll try it your way. Can you give an example of how you'd actually start that approach? Just start coding up the ideal interface?
Matt
Marcus, that's an interesting point. Can you give more details regarding the way you write your code without having the exact database schema.Start with some user interface code and fake the database whilst you work on it.
What do you start from? Pieces of code that don't touch DB at all? When is the best time to start creating tables in db?
Thank you
CEO of Grandiz! - a team based in Odessa, Ukraine.
Designing and developing sophisticated web apps on CodeIgniter!
Thank you everyone. Serialize was what i was looking, and i'll read that tutorial!
lastcroft:
sounds like a good idea. but what you mean exactly when you say "fake the database"? When you exactly start your database schema?
Andreas
Hi...
There is actually an example going on right now in the advanced forum (the RBAC one). As was pointed out, just write your ideal code.
Say you want to write a protected page...
Sorry, but I am a bit of an OO bigot. A procedural version would look similar though.PHP Code:<?php
require_once('stubs/authenticator.php');
$authenticator = &new Authenticator();
$login = &$authenticator->login('Me', 'Secret');
if (! $login) {
header('Location: error.php');
}
?><html>
...
</html>
Now the stub version of the Authenticator...
You may want to stub the Login as well. Once the application design has settled down, you can start to replace the stubs with the live versions. It's really just an enabler for top down design. It's crude, but you can achieve a lot with this. Not least you can get a prototype in front of the customer that much faster.PHP Code:<?php
class Authenticator {
function Authenticator() {
}
function login($name, $password) {
if (($name == 'Me') && ($password == 'Secret')) {
return new Login('Me');
}
return false;
}
}
?>
yours, Marcus
Marcus Baker
Testing: SimpleTest, Cgreen, Fakemail
Other: Phemto dependency injector
Books: PHP in Action, 97 things
um, how shall i put this... no
"fake the datebase" is a great idea, and i endorse this
but it works only down at the functional level
"add a customer", "remove from cart", "list available quantities", "search for similar items"...
no way can you code this without knowing what the database is going to look like
perhaps i misunderstood you, marcus, and you were referring to the database schema as the final exact table keys, column datatypes, indexes, etc.
okay, that works, i guess
it's more or less compatible with my advice --
if i was only allowed to give one piece of database project advice it would be this: do the database logical design first
To me it seems that it may not matter (although I'm really liking the idea Marcus presented), just as long as at some point the two meet up together. Right? Am I being too obvious?
Matt
I think the opinion of sorting out the DB schema last is a bit strange as well.
I find it best to just completely forget about the code and the computer altogether, then just sit down and plan the DB schema on paper first. Think about what data will need to be stored, how you will retrieve it (SQL statements), then actually start putting a few dummy entries in there.
Then it's just a simple case of doing the DB input, output and update scripts and if there is already data in there, you'll easily see where ur going wrong with your code.
Just my opinion.
Hi...
Absolutely, but then this is the customer requirements view and at the start of a project is at it's most volatile. It is also the domain layer view for four tier web apps. In fact the more time I spend in this business the more I am moving away from structural thinking into roles/functional thinking, but that is an aside.
Actually I find you can, but yes this is about the limit point. The limit point drops back further if you have stored procs. or views guarding some of the data. It swings the other way if you are holding off on paradigm (we are moving a lot of operations to full text engines at the moment) or have performance/caching issues.
Yessish, as I actually do mean basic stuff like choice of tables. The minimum amount of one to many relationships will become apparent as the project progresses, but these will appear in code anyway. The application will have code something like...
This is the crunch point and I can fake this, but go no further. The DB hat goes on, but by now things should be pretty stable enough for some pretty steady data modelling and integration work.PHP Code:$finder = &new PeopleFinder();
$results = &$finder->findByMarketingCategory('prospect');
while ($person = &$results->next()) {
$person->contact($promotional_mail);
}
Flies in the ointment include existing schemas and big communication screw ups trying to predict the future. Hey, I am just reordering the problems, not solving them.
yours, Marcus
Last edited by lastcraft; Apr 14, 2004 at 12:23.
Marcus Baker
Testing: SimpleTest, Cgreen, Fakemail
Other: Phemto dependency injector
Books: PHP in Action, 97 things
I've been working on one application a while ago and faced a problem:
At first - I've written down the DB schema on a piece of paper. Then - Gave a lot of thought to each table, each element, etc. etc. , corrected some things and then - when I thought this will be the final structure of my DB - I created it. Only after that I've started writing code to fit this schema.
While I've been writing code some new ideas arose which I desperadely needed to implement, I found that some things could be achieved easier, etc, etc.. All this involved tweaking the db structure - and this all resulted in a huge mess in my head.
That's why I think I'm going to follow advices given by Marcus and start coding first, having only an approximate db schema in mind and on a piece of paper.
Then - when code that doesn't involve databases is finished - I'll have a deep look through it, write down the exact db structure needed, create the database - and start coding the parts of code that will communicate to the db.
Hope this works and this won't result in a new mess in my head.
Thanks Marcus
CEO of Grandiz! - a team based in Odessa, Ukraine.
Designing and developing sophisticated web apps on CodeIgniter!
I find it best to just completely forget about the code and the computer altogether, then just sit down and plan the DB schema on paper first. Think about what data will need to be stored, how you will retrieve it (SQL statements), then actually start putting a few dummy entries in there.
Can understand where Marcus is coming from, though this point is more intended for non critical - as I see it - functionality. A log in is ideal, as it's simple. More complexity could - proberly in fact - require a database schema already there before hand
analysis is needed when u design a database..
for supreme and fast access to database.. the database need to be separated..
its like when u put
> personal info
- sub - payment = paid
this gonna be a hard one..
as u must separate the account and personal info. to make the database better and faster access..its my thought duh
At the start of the project everythig is volatile. I find your advise interesting, but I thing it needs to be qualified.
What is the "Limit Point"? I have never heard of it....
And this is exactly the way one should not proceed with a project. This is the approach known as "learning to fly by jumping out of the window". Sure, you can learn as you go, but the likelihood of you running out of time and hitting the ground is much higher.
Take the following advice:
You can start coding (or doing logical DB design) when:
I'll explain all five points:
- You know exactly what you have to build to make the customer happy
- You know exactly how to build what the customer wants
- You know when you will have finished coding
- The customer has approved the prototype
- You have identified the risk areas and approved a plan with the customer for sharing risk
One:
Every customer has a list of goals they want to achieve with their project, it is our jobs to first discover those goals. If you really do not know what your client is hoping to achieve, there is no way except luck that you can help her achieve those goals.
Two:
Ok, so you know what the customer wants, great. The next mistake is to say "I always wanted to learn DOTNET|PHP|RUBY|JAVA|MySQL, lets use that". If you don't know how to use your tools you are not going to make it. (On the other hand, a man with a hammer calls every problem a nail, so it is important to learn new things too).
Three:
Related to point one. It is all too easy to gold plate your design, your code, your database. Overengineering or overdesigning is the most common mistake I have seen, nobody can say when the job will be done. An anology: if you set out to paint a white building white, but nobody lets you walk all the way around to see it, how will you know when you have finished?
Four:
You did make a protoype, right? Prototyes are always one of two kinds: Wide and shallow: you can prototype your ecommerce website just by creating all of the pages necessary in vanilla HTML: no fancy design. Then you know how big your site will be.
Narrow and deep: you can prototype your ecommerce website by programming one facility from the user interface all the way down to the nitty gritty: adding an item to the basket is a nice example, including nice design, form validation, database tables and access code, notification services etc.
Doing both one wide and shallow and one narrow and deep prototype will practically remove all of of the volatility and ambiguity in your project specification.
Manage risk:
Every project has risks involved: can this technology deliver adequate performance, will the customer pay, will the key programmer leave the company, will the requirements change. The key to succesfully managing projects is to identify, assess and plan for risks. Requirements drift is a major risk, when the client continually respecifies what they want. This is a financial nightmare if you have a client like this.
So, Marcus, I see where you are coming from. Designing and implementing the DB too soon is likely to be a waste of time, causing untold misery whilst developers fight to turbocharge a donkey of a database when really it should have been shot weeks ago.
But I don't think the problem is limited to the database, it is clearly also the same problem when related to application code, and I could argue that committing to a screen design too early is also problematic.
Cheers,
Richard
This is not b******t advice, I feel qualified totalk about this subject.
very good post, asterix
rep++ for you
I think Lastcraft is talking about agile programming methodologies. In part, these avoid commiting yourself to decisions until you have to. It's more like learning to fly in little steps, in a flight simulator. You can try out ideas without leaving any smoking holes in the ground which are expensive to clear up. There's something badly wrong if you can't do this ie if the data source can't be swapped out easily - or mocked during development.
Choices in the data source layer (DataMapper? ActiveRecord? TableDataGateway? etc) are heavily dependent on the complexity of your domain logic which is a very good reason to get the latter sorted out first.
Iterative development might use something like Scrum to manage the process of keep-making-little-steps-forwards. With testing, you know you've finished coding (for the moment) when all the tests pass. Agile methods have a different perspective on the design problem. They don't see the program as a fixed entity which is possible to define, build and walk away from. Requirements will change, or new ones will be added.
Finally, an interesting article from Fowler: Is Design Dead?.
Well, this is turning out to be quite an interesting debate on software engineering
Agile, XP, Test driven, data driven, model driven, gas fired. Development methodologies come and go, and are always a response to the previous worst practice.
In the eighties a common bad practice ws to not document things well enough on (seriously) large projects, so the idea of requirements engineering was born. If only every single specification could be traced from initial mention, all the way down to an individual method source code then the world would be fine! And it was true, the problems of people looking at code, but not knowing "why" it was coded like that disappeared. Process oriented design methodologies were born of this, including waterfall.
Then it was seen that projects were taking too long! We couldn't wait 18 months for the software! So along cam incremental development. This month the customers could have data entry, next month the reports. the month after we would deliver another module. Problem solved.
But wait! After 18 months of this kind of work we noticed that nothing fitted together any more! Each module required such a major rewrite of the other modules each iteration took increasingly longer! So along comes the idea of iterative programming. You program an oinion, every release adds another layer on top of the onion, perfect, the code remains in sync. But then again release times increase too! And we still have to rework the code all the time
Some people thought that components would solve all this trouble. Just plug in some ready made "order processing" or "payroll management" component and all worries are solved. This was around the time when CORBA, COM and the rest were born... Until people realised that there is no such thing as a standardized component in custom software development...
Some bright people put 1 and 1 together and hey presto, the idea of iterative and incremental programming was born! Short release cycles! Cohesive code modules! All solved? No. Customers - who are now getting software changed and released on two week cycles - wanted more (of course). They became so pampered, and the software guys had this perfected so well, that the game changed again. Now the customers want excellent code, short release cycles and the right (often contractually specified) to change the requirements at the drop of a pin.
Not even iterative and incrmental development methodologies could cope with requirements which are so volatile nobody is even prepared to write them down. So along came the idea of eXtreme Programming, lets not write down requirements, they'll have changed before the ink is dry. So let's get the customer on the team, let's do a planning game with chips instead of man-hours and we will work in pairs.
And indeed, XP goes a long way towards solving the problem it was created to respond to. And Scrum for that matter, although it beats me why anyone wants to add a layer of pseudo-project management to an oh-so agile customer focussed pair programming test-driven and refactored project team. People seem to forget that XP was born of the DSDM methodology, go look it up if you are interested.
So what I am saying is this: the current vogue in project methodology, whatever it's name or practice does these things:
a) it brings useful new development practices (not even Codd modelled databases using something as neat as ErWin).
b) it attempts to solve the current worst problem
c) it will surely be replaced in n years by the next repsonse to the next problem
And all the time, common sense and experience tells us, if you don't know what the software should do, you will not be able to please your customer. If you don't know when to stop coding, your project will fail. If you don't know how to program the specifics, you are doomed. If you don't manage risk, you will always be surprised, will always need new excuses, and ultimately, probably slowly and painfully, fail.
This advice is IMHO, independent of the specific methodolgy you profess, you can use it with agile, with rup, with waterfall, with spiral, with DoD, whatever you like.
This is a data access strategy, it has very little to do with the domain complexity. It is an architectural decision which is taken often before program specifics are known. If you are designing a high visibility, high performance, sensitive transaction aware ECommerce site, one does not choose active record, period.
Yes, this is agile lore, and very good it is too. I like a lot about some of the agile practices. I just don't believe in the one true gospel according to Beck / Fowler / Highsmith / Ambler insert Guru here, and from the conversations I've had with them, they don't either.
I see where you are going though, if I may presume your question: "When do I decide to code the database first, do the screen design first, or do some real programming first?".
Well, how do you know when to boil the potatoes or roast the meat first when making your festive family meal? Experience I guess. But here is how I do it when I am not sure:
I have seen many projects fail because of idiot managers who say "Oh, I see that we have a problem here. Let's not stall the project completely, so we'll put this task back in the schedule". Sweeping project problems under the carpet does not make them go away, they just get bigger and nastier when no one's looking. If this is done even twice you have an unknowable und uncontrolled project, no matter what methodology you are using.
- If I am not sure about how a (website | software) will look, or how usable it will be. This is then the biggest risk, so I do screen design first.
- If I am not sure about the database design or the enitity model, I do it first. again, I remove the biggest risk first.
- If I am not sure I can use the language | database | tool chosen, I start here first. If it's not going to work like this, I'd rather know about it right now than next week.
- Richard
Posting Permissions
Bookmarks