On 20-Oct-2001 Mike E wrote:
> That seems to follow how I've been doing it as well.
>
> For example, consider this database set up:
>
> Table: news
> Fields:
> newsid int(10)
> userid int(10)
> dateposted datetime
> subject varchar(255)
> content text
>
> Table: users
> Fields:
> userid int(10)
> name varchar(60)
>
> and on and on. I'd love to hear from some hard-core developers. Guys who
> have been doing database development professionally for years.
>
> Mike
My preference is for the table where the id is the primary key is called 'id';
and references to it are {ref}id.
In your example above:
Table: news
id int(10)
userid int(10)
dateposted datetime
subject varchar(255)
content text
Table: user
id int(10)
name varchar(60)
$qry="SELECT dateposted,subject from news as n, user as u
WHERE u.id=n.userid GROUP BY u.id";
This also simplifies generic Perl routines like:
sub ClearOrphan { # un-tested cut-n-paste code
my $pri = shift;
my $sec = shift;
my $ref = shift;
$qry="SELECT $pri.id FROM $pri LEFT JOIN $sec
ON ($pri.$ref = $sec.id) WHERE $sec.id IS NULL";
my @ids=GetArray($qry);
while ( my(@id)= splice (@ids,0,64)) {
$qry="DELETE FROM $pri WHERE id IN (" .join(',',@id) .")";
SQLQuery($qry);
}
}
ClearOrphan('news', 'user', 'userid');
will delete any news articles that don't have a valid user ...
---
I also try to avoid plural table names:
users -> user
hosts -> host
urls -> url
host.id reads much cleaner than hosts.id (to my eye).
Agree with the thread on no upper-case/mixed-case names.
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
