Nathan Thank you for your excellent explanation! One of the reasons I love this board is the vast knowledge that people are willing to share. I believe I understand the importance of normalization, however one of my original questions seems to still stand. If normalization is so important, why is it that the INSERT INTO multiple tables is not a standard command or procedure?. I'm not saying it has to be easy, but it should be well known. I really thought when I first asked the question I was going to get multiple similar answers, or someone was going to look at my script and tell me I omitted some simple puncuatuion (or other simple mistake)...which has not been the case. Even if the answer were "Cant be done", you need to write a separate script for each insert, that would be ok. But I have to think that someone reading this board has accomplished, somehow, writing to separate tables in the same DB. Again, thank you for all the information and your time. Gary "Nathan Rixham" <nrixham@xxxxxxxxx> wrote in message news:4BC21B88.3090408@xxxxxxxxxxxx > Gary wrote: >> Adam >> >> Thank you for your well thought out response. >> >> Two points: >> >> I did not include any anti-injection functions because this was an >> experiment for multiple tables, it is on my machine only. >> >> Since these are php scripts, I dont think anyone will mind (not to >> mention >> this board always provides great answers)., >> >> However I think I may have answered my question about the importance of >> normalization of tables. I have written a number of databases used on >> various web sites, however, they all are used as a collection of data >> from >> input forms. >> >> Most of the information about mutilple tables deals with the retreval of >> data from, not inserting into, meaning they are more used for known data >> inserted by the database owner/administrator to be retrieved by queries >> into >> the DB. >> >> Am I on the right track? >> > > I'm unsure if this is of use to you or not (and it has been covered in > part already), but here goes: > > Generally when working with database tables, we normalise, or split > information up at natural points where you have a greater than 1-1 > relationships between the data items. > > An example may be a table structure to store User, Blog Post and Comments. > > With this common example it's very inefficient to store all the > information in one table, because it is split naturally in to three. > > We have three relationships here; all 1-* (meaning "one to many"). > > 1 User to * Blog Posts > 1 Blog Post to * Comments > > Thus naturally, and on first glance we would have 3 tables: > > | Table User > -------------------------------------- > | UserID | Username | Password | ... > > > | Table BlogPost > -------------------------------------- > | PostID | PostTitle | PostersUserID | ... > > > | Table Comment > -------------------------------------- > | CommentID | CommentOnPostID | CommentersUserID | ... > > > As you can see from the above, all of the rows (or items) in our tables > are linked via IDs to each other. > > We can further normalise the above tables to take in to account *-* > (many to many) type relationships, and to fully separate cross cutting > concerns. For instance it may be that a BlogPost has 3 different Users > as author(s). > > aside: cross cutting concerns can be considered as something (in this > case a table) trying to handle something which is of no concern to it > (in this case the BlogPost table needs to be aware of Users and their > IDs). > > To handle the aforementioned we can introduce something commonly > referred to as "link tables", consider: > > > | Table User > -------------------------------------- > | UserID | Username | Password | ... > > > | Table BlogPost > -------------------------------------- > | PostID | PostTitle | ... > > > | Table BlogPostAuthors > -------------------------------------- > | BlogPostID | UserID | > > > The "link table" BlogPostAuthors acts as a many-to-many join table > between BlogPosts and Users. Similarly we could introduce the same kind > of link table between BlogPosts and Comments, / Users and Comments. > > In a real system we may even have another two primary tables introduced, > Roles and UserRoles, as the system may have multiple Roles (Author, > Commenter, Admin etc) and each User may have multiple Roles themselves, > in one capacity I am an Admin, in another I'm an Author. (UserRoles may > be better considered as Personas?) > > Ultimately there are many considerations to take in to account, the > relationships between types of data, the frequency at which > inserts/updates/selects occur, the complexity and speed of each query, > and much more. > > Designing a table structure is different for each job, with different > considerations and things to weigh up, generally though normalisation > can cater for at least some future scope creep. > > It's also worth noting that some consider it bad practise to design a > system from the storage point upwards, because the application and data > should not be constrained by persistence layer features or limitations - > which would indicate designing the data model in UML or suchlike and > dealing with Objects rather than Tables (then later mapping objects to > tables in order to persist them, if choosing a RDBMS as the persistence > layer). > > It may also be worth noting that an EAV model is the ultimate in > normalisation and allows all data to be persisted in a single 3 column > structure (or 4 if you partition data). I'll save details of this though. > > Do hope that helps in some way, and if you need any more info just shout. > > Nathan > > __________ Information from ESET Smart Security, version of virus > signature database 5021 (20100412) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 5021 (20100412) __________ The message was checked by ESET Smart Security. http://www.eset.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
