SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    Where's my title at? dreaz's Avatar
    Join Date
    Apr 2004
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Good database structure

    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

  2. #2
    SitePoint Zealot Pozor's Avatar
    Join Date
    Apr 2004
    Location
    Switzerland
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    you can use serialize() and unserialize() to store and restore an array (serialized array stored as text or varchar...)

    greez Pozor

  3. #3
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by dreaz
    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:


    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

  5. #5
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Addict Tim_Rogovets's Avatar
    Join Date
    Aug 2003
    Location
    Odessa, Ukraine
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Start with some user interface code and fake the database whilst you work on it.
    Marcus, that's an interesting point. Can you give more details regarding the way you write your code without having the exact database schema.

    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!

  7. #7
    Where's my title at? dreaz's Avatar
    Join Date
    Apr 2004
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by Tim_Rogovets
    Marcus, that's an interesting point. Can you give more details regarding the way you write your code without having the exact database schema.
    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...
    PHP Code:
    <?php
        
    require_once('stubs/authenticator.php');

        
    $authenticator = &new Authenticator();
        
    $login = &$authenticator->login('Me''Secret');
        if (! 
    $login) {
            
    header('Location: error.php');
        }
    ?><html>
        ...
    </html>
    Sorry, but I am a bit of an OO bigot. A procedural version would look similar though.

    Now the stub version of the Authenticator...
    PHP Code:
    <?php
    class Authenticator {
        function 
    Authenticator() {
        }
        function 
    login($name$password) {
            if ((
    $name == 'Me') && ($password == 'Secret')) {
                return new 
    Login('Me');
            }
            return 
    false;
        }
    }
    ?>
    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.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by lastcraft
    If I was only allowed to give one piece of database project advice it would be this: Do the DB schema last.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    May 2003
    Location
    virginia
    Posts
    988
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    UK
    Posts
    152
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by r937
    um, how shall i put this... no


    Quote Originally Posted by r937
    but it works only down at the functional level
    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.

    Quote Originally Posted by r937
    no way can you code this without knowing what the database is going to look like
    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.

    Quote Originally Posted by r937
    perhaps i misunderstood you, marcus, and you were referring to the database schema as the final exact table keys, column datatypes, indexes, etc.
    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...
    PHP Code:
    $finder = &new PeopleFinder();
    $results = &$finder->findByMarketingCategory('prospect');
    while (
    $person = &$results->next()) {
        
    $person->contact($promotional_mail);

    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.

    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

  13. #13
    SitePoint Addict Tim_Rogovets's Avatar
    Join Date
    Aug 2003
    Location
    Odessa, Ukraine
    Posts
    221
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    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!

  14. #14
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database structure.. is hard to design..

    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

  16. #16
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    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.
    At the start of the project everythig is volatile. I find your advise interesting, but I thing it needs to be qualified.

    Quote Originally Posted by lastcraft
    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.
    What is the "Limit Point"? I have never heard of it....

    Quote Originally Posted by lastcraft
    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.
    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:


    1. You know exactly what you have to build to make the customer happy
    2. You know exactly how to build what the customer wants
    3. You know when you will have finished coding
    4. The customer has approved the prototype
    5. You have identified the risk areas and approved a plan with the customer for sharing risk
    I'll explain all five points:
    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.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    very good post, asterix

    rep++ for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by asterix
    ...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".
    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.

    Quote Originally Posted by asterix
    You can start coding (or doing logical DB design) when:
    1. You know exactly what you have to build to make the customer happy
    2. You know exactly how to build what the customer wants
    3. You know when you will have finished coding
    4. The customer has approved the prototype
    5. You have identified the risk areas and approved a plan with the customer for sharing risk
    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?.

  19. #19
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, this is turning out to be quite an interesting debate on software engineering

    Quote Originally Posted by McGruff
    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..
    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.

    Quote Originally Posted by McGruff
    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.
    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.

    Quote Originally Posted by McGruff
    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..
    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:

    1. 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.
    2. If I am not sure about the database design or the enitity model, I do it first. again, I remove the biggest risk first.
    3. 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.
    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.

    - Richard


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •