Re: DB design

Hi, just wondering ... If I have a database called 'users' with - userid - name - address - lastLogin - defaultview (a viewid from below) etc and 'views' with - viewid - colour - bgcolour - othersettings - moresettings etc and i want them to have a many to many relationship, do i create another table with: - viewid (primarykey) - userid (primarykey) - anotherfieldperhaps etc or can the fields above not have to be viewid/userid? How do i facilitate viewing, inserting, deleting records? Eg, if i want to find the default colour for userid=3 ... I don't quite understand what the syntax should be ? Is this correct ? SELECT views.colour FROM views WHERE views.colour = users.defaultview AND users.userid = '3' Eg, if I delete tableid '5', then i need to also delete all the records in the 3rd table where tableid '5' as well. I can write a second SQL statement, sure, but can't you build relationships where this sort of thing is done more automatically (thus is faster?). Thanks for your time, Siggy ----- Original Message ----- From: "Finkel, Sean" <finkelsd@ssg.navy.mil> To: <php-db@lists.php.net> Sent: Thursday, December 21, 2000 7:02 AM Subject: RE: [PHP-DB] DB design > > Personally I would make three tables: > 1. A table of catagories > 2. A table of 'items' (which you have) > 3. A table that holds item ids and ctagory ids. with PK being both fields. > > If you need me to expand on that, let me know =) > > Sean > -----Original Message----- > From: Tom Harris [mailto:stry_cat@yahoo.com] > Sent: Wednesday, December 20, 2000 12:44 PM > To: php-db@lists.php.net > Subject: [PHP-DB] DB design > > > Here's my problem... > > Each item in my database can belong to several of 50 categories. Right now > I'm using a text field which contains a string of letters representing each > category. (i.e. item A belongs to categories one and five so it's category > field is " ONE FIV " To find data I use a select statement with a WHERE > category LIKE "% FIV %" which gets everything that belongs to category 5.) > What I want to know is there a better way to do this? It is possible that > the number of categories will double in the near future. I'm using PHP > 4.0.3 and MySQL 3.22.32 to create a dynamic website, right now it's getting > may hits, but it is possible that could change as well. > > Thanks, > > -Tom > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > >