Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

one table or multiple?

owen1owen1 Posts: 76Member
I am using mysql (although it doesn't matter). Which is more EFFICIENT. I am writing a program which has all the data in one table, is this faster (for the database) than splitting it up the data into multiple tables?
[hr]
http://owensoft.20m.com
check out my current projects: http://www.owensoft.net

Comments

  • JonathanJonathan Posts: 2,914Member
    Hi,

    You need the database to be fully normalised. The best way to do database design is to think about what objects you are storing data about. If you are storing details of who owns what car, you could do one table for that, but it'd be better to do one table for owners, a table for cars and a table saying who owns what cars. That way you only need to store the various models of cars and the various owners, then people can own multiple cars and a certain type of car can be owned by multple people. Of course, you'd want to but number plate in the link table saying who owns what car.

    I hope this gives you some insight, though admittedly db designs ain't always the simplest thing to work out.

    If you have a specific problem, let me know and I may be able to tell you how I might approach it.

    Jonathan


    -------------------------------------------
    Count your downloads:
    http://www.downloadcounter.com/
    And host your site:
    http://www.incrahost.com/
    Don't say I never give you anything... ;-)

  • owen1owen1 Posts: 76Member
    : Hi,
    :
    : You need the database to be fully normalised.

    apparently you didn't read the post? why normalise the database? is it faster for the database or is it easier for the managers to read?

    anyway that's not the point - the point is - "IS IT FASTER TO RUN YOUR PROGRAM FROM ONE TABLE OR MULTIPLE TABLES" , disadvantage + advantages
    [hr]
    http://owensoft.20m.com
    check out my current projects: http://www.owensoft.net

  • JonathanJonathan Posts: 2,914Member
    : apparently you didn't read the post?
    Evidently, I must have done to have a clue how to write a related reply. If you look at my example, you'll see that normalisation affected whether we had 3 tables if we did it, or 1 if we didn't. 3 = multiple tables. 1 = 1 table. Get it now?

    : why normalise the database? is it faster for the database or is it
    : easier for the managers to read?
    It's actually harder to read in some ways as you have to look through multiple tables to find stuff...

    : anyway that's not the point - the point is - "IS IT FASTER TO RUN
    : YOUR PROGRAM FROM ONE TABLE OR MULTIPLE TABLES" , disadvantage +
    : advantages

    I stand by what I said. At the end of the day, I think the best way to do your database is to normalise it properly. Sure, selections work a bit faster if you only have one table as the db engine doesn't have to do the joining of tables. That's great until you realize that your updates are getting more complicated and you're having to update multiple records when things change, and then you run into data intergrity problems if an update or deletion goes wrong.

    If it's a really simple problem you might get away with one table and it'd be no big deal and your selections may run faster. If you're doing anything more elaborate, you should probably normalize it (e.g. have proper one-to-may relations between tables). Otherwise, the time you save on selects you only pay back on updates and deletes, plus you in writing your query may well spend more time trying to make sure you are deleting/updating all the right records rather than just one.

    I guess what you call efficiency may be different to what I do - if a query runs 10ms faster, but took me a minute longer to write, it must run 6000 times before it gets more efficient. E.G. efficiency isn't just about how fast the code runs, but how fast it is to write too in some aspects. Just a thought...

    Hope this helps clear things up,

    Jonathan

    -------------------------------------------
    Count your downloads:
    http://www.downloadcounter.com/
    And host your site:
    http://www.incrahost.com/
    Don't say I never give you anything... ;-)

  • owen1owen1 Posts: 76Member
    I get what you are saying. I am currently trying to use only one table at the moment, I did the normalisation thing and it works to a point except that when I start changing the database - I have to keep updating the various tables and joins that are currently working. normalising the database is very logical but it's not very "programmer friendly" if you get what I mean.

    I am currently trying to build a program(phpforum) in one table (actualy 3 tables, but one main table), I would have finished it already if I had normalised the tables. I have had a couple a problems so far mostly with relationships in the same table, and user permissions but I will find a way.

    anyway thanx for your help, seeya
    [hr]
    http://owensoft.20m.com
    check out my current projects: http://www.owensoft.net

Sign In or Register to comment.