* Neil Watson > I'm thinking about an addressbook database. I would like an opinion on > my table layout (I still consider myself a mysql newbie): > > table user > uid > username > timestamp > > table secret > uid > secret > timestamp
You don't mention keys? Why do you want to split the user table? A more 'normal' approach would be something like this: table user uid INT PRIMARY KEY username varchar(30) secret varchar(16) created datetime modified timestamp You probably want an UNIQUE index on username, presuming this is what the user is going to use to log in to the database. The same index is used both to ensure you don't insert duplicate users and to do fast lookups on the users when they log in. Note that I added an DATETIME column and gave a name to you TIMESTAMP column. Be aware of the difference between the data types DATETIME and TIMESTAMP: A TIMESTAMP column is changed automatically when any column in the row is changed, and it is set automatically to the current time when the row is created. <URL: http://www.mysql.com/doc/en/DATETIME.html > If you are going to use a web based front-end on your address book database, you may want a column in the user table for email address. A column for last_login could also be usefull. A 'hack' that could be usefull: add an pid to the user table. This pid points to a person-record for the user, i.e. with the users name, birthday, addresses and so on. The uid for that people record could be NULL if you don't want the user to be able to change his own address info (billing address?). Otherwise it is the uid for the user, and he can edit his own info in the same way as he edits any other person. Your application should probably prevent the user from deleting the person representing the user, even if you allow him to edit it. The user interface could also present the users personal info in a different way than other persons. > table people > pid > uid > firstname > lastname > birthday > timestamp The uid in the people table will prevent users from 'sharing' persons, but this may be just what you want. To be able to 'share' persons among users, you would have to remove uid from the person table, and use a separate table 'user_people' with the columns uid and pid. The pid column should be an integer, and it should be the primary key of the people table. The uid column should be of same type as the user.uid column, and it should have an index, alone or as the first part of a combined index. You should probably have an index on (uid,lastname) and (uid,firstname), but ... There is a weakness in this table design, the significance depends on the amount of persons you expect to handle for each user. The problem is that some persons have multiple first and/or last names. This prevents you from using ... WHERE ... LIKE '$criteria%' ... (Starting with the search criteria, ending with %). This is the syntax you need to use to utilize the index. If this is a 'normal' address book application, and you expect a few hundred persons per user, you should be okay, you can do scans through all people for the user with ... WHERE ... user.uid=people.uid AND people.firstname LIKE '%$firstname%' and people.lastname LIKE '%$lastname%' (note the leading %), and the index won't be used/needed. > table address > aid > pid > address > city > province > country > code > address_type > timestamp The aid column should be an integer primary key, and the pid column should have an index. Again, if the amount of data is big, you should normalize this table further. You should have separate tables for country, city and province. With a small amount of data this may be overkill, the same issues as for the people table applies. > table email > eid > pid > alias > string > email > timestamp The ied column should be an integer primary key, and the pid column should be indexed. I'm not sure what 'alias' and 'string' is supposed to store? Is it directly dependant on the email address? I guess 'alias' is to be used as a real name when sending email to this address? You could use CONCAT(people.firstname,' ',people.lastname) for that... or? I would expect the email table to contain only eid, pid, email and the timestamp column, if you need to know when the address was last modified or created. I would have expected a similar table for phone numbers, optionally related to address: table phone phid INT PRIMARY KEY pid aid phone varchar(12) You would as usual need an index on pid, and one on aid. The aid column must accept NULL, which means a phone number is related to the person directly, not to the address (for mobile/cell phones). If you normalize more and have a separate country table, this table could also store the country code, and your application could for instance automatically add the right country code when the user lists foreign phone numbers. In that case you would probably also need a country code in the user table, unless all your users are guaranteed to be from the same country. > Thoughts? I think that was it. :) HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
