Subject:
|
Re: SciBrick is looking for you!
|
Newsgroups:
|
lugnet.org.scibrick
|
Date:
|
Mon, 25 Oct 2004 20:54:48 GMT
|
Viewed:
|
6105 times
|
| |
|
|
In lugnet.org.scibrick, Bram Lambrecht wrote:
| |
In lugnet.org.scibrick, David Eaton wrote:
| |
In lugnet.org.scibrick, Bram Lambrecht wrote:
| |
I am...here’s the database structure I came up with (with a couple notes):
TXT file
|
I might suggest combining sb_m, sb_m_loc, and sb_m_profile into sb_m,
and just not fill in the _loc and _profile bits unless either supplied or
member is a “full” member. Unless people can have multiple locations and
profiles...
|
I think I might have required unique member ids on those tables too. The
idea was to keep the number of columns in sb_m down, but maybe that’s not
really necessary.
|
Depends how much space you need to save, I guess. If you’re using varchar’s or
text fields (for profiles certainly), you’re only adding an extra... 2 bytes for
a null entry? Forget if it’s 1 or 2. So maybe 18 bytes extra per member. I guess
when it comes to stuff like that I rarely think about space requirements anymore
:)
Here at work, I built a more dynamic user system where pretty much 100% of the
fields aren’t even fields, but are actual database entries themselves. Hence,
you only would get an entry for “address” (say) when the address was actually
filled in, even though your entry form would still have it show up. And until
then, the db query would just be set up to return the default field value if the
field was null. Saves on space, I guess, but that was more of a side effect in
that case :)
| |
| |
Also, you might be able to combine worldcity and zipcodes, and “if country
!= ‘USA’, go by country/city, else go by (state/city) or (zipcode)”. I
suppose technically that might take up more space for the table if you set
up an extra key for country/city... Eh, either way I suppose. Although
admittedly, that’d give you only one place to look if you were trying to
reference by long/lat. Hmm.
|
The zipcodes table is 40,000+ rows, while the world cities table is only a
couple hundred rows. Doesn’t make much sense to combine them, since it would
make it more difficult to add cities to the world table.
|
Yeah, there’s not terribly much benefit if you’re going to bounce the long/lat
over to the member entry anyway-- Where it would make more sense is if you had
something like:
Member:
------------
member_id
...
location_id
...
Locations:
-------------
location_id
city
state
zipcode
country
longitude
latitude
Hence, if you ever wanted to crossref, say, an event with the location table,
it’s all there in one table. But with the longitude latitude stored in the
member entry, and the zipcode/worldcity tables more just for lookup purposes,
it’s not as necessary.
| |
| |
| |
As you can see, there is quite a bit of information that needs to be
entered and displayed. Error checking is the worst part of it,
|
Is this error checking insofar as existing members/officers OK’ing people’s
information?
|
No, more along the lines of making sure the data people enter is the valid
type/size/etc. Automatic error checking with appropriate error messages and
a chance to correct the errors.
|
Yeah, some of that is tricky. Some stuff is easy to handle in a regexp (phone
numbers, zip codes, email addy’s), and some is tougher if not impossible.
| |
| |
| |
I also want to make sure members get signed up for
appropriate mailing lists.
|
Ooo, there’s another two tables for you :) Mailing list info & members/list
crossref
|
Unfortunately, no. I don’t have access to the mailing list files, so members
will have to be subscribed to the list via an email sent from the
scibrick.org server to the mailing list server.
|
Dang. I guess that does indeed make it trickier...
DaveE
|
|
Message has 1 Reply:
 | | Re: SciBrick is looking for you!
|
| (...) It was more a matter of keeping the table easier to read and deal with for things like just listing members, which doesn't require a full bio and location, than of actually saving bytes. (...) The idea was to avoid lookups in those huge tables (...) (20 years ago, 25-Oct-04, to lugnet.org.scibrick, FTX)
|
Message is in Reply To:
| | Re: SciBrick is looking for you!
|
| (...) I think I might have required unique member ids on those tables too. The idea was to keep the number of columns in sb_m down, but maybe that's not really necessary. (...) The zipcodes table is 40,000+ rows, while the world cities table is only (...) (20 years ago, 25-Oct-04, to lugnet.org.scibrick, FTX)
|
74 Messages in This Thread:
 
 
- Entire Thread on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
This Message and its Replies on One Page:
- Nested:
All | Brief | Compact | Dots
Linear:
All | Brief | Compact
|
|
|
|
