Differences between revisions 56 and 57
Revision 56 as of 2003-05-08 14:11:15
Size: 6808
Editor: DonPark
Revision 57 as of 2003-05-10 11:59:21
Size: 6808
Editor: DonPark
Deletions are marked like this. Additions are marked like this.
Line 69: Line 69:
 * latitude - character varying(200) - latitude
Line 70: Line 71:
 * latitude - character varying(200) - latitude

The community database is the foundation for a set of social software applications. The primary focus is a user roster, a node roster, and a whuffie/mojo calculation and management system. The database is currently implemented in SQL tables on postgresql.

community database take two. March 2003 AdamShand, JerrittCollord, AaronBaer, DonPark, (insert your name here)

Users Table BR Description

  • A record in the user table refers to an individual person.

Table Relationships

  • 1...member of.......N Communitygroup


  • id - numeric, primary key - Unique ID
  • createdate - timestamp - record creation date
  • modificationdate - timestamp - last modification date
  • username - character varying(20), unique - login name
  • firstname - character varying(50) - The user's first name
  • lastname - character varying(100) - The user's last name
  • password - character varying(8) - The user's password, in plaintext
  • memberstatus - character varying(10) - An index into the memberstatus table
  • communitygroupid - numeric - An index into the communitygroup table
  • phone_home - character varying(15) - Home phone number
  • phone_cell - character varying(15) - Cell phone number
  • url_homepage - character varying(200) - URL for a home page/info page
  • url_profileimage - character varying(200) - URL for a profile image
  • url_foaf - character varying(200) - URL to a FOAF description
  • comments - character varying(2000) - miscellaneous comments
  • certificationid - numeric - An index into an adhoc certification table
  • mailto - character varying(100) - User's email address
  • jabber - character varying(100) - User's jabber id
  • moinlogin - character varying(24) - login to the moinmoin system
  • sponsorid - numeric - Index to the user who sponsors this person
  • postaladdressid - numeric - Index into the address table

Table attributes - Cascade BR

Create Record BR

  • Generate a Unique ID - either ask the user or generate one randomly
  • Minimum fields to fill: userid

Delete Record BR

  • Remove the record in the User table
  • Remove records in all tables listed in the relationship table list that have the same unique id

Node table BR Description

  • A wireless accesspoint at a particular location.

Table relationships

  • 1...owned by..............N User
  • 1...managed by............N User
  • 1...upstream comes from...N Provider


  • id - numeric, primary key - unique ID
  • createdate - ? - record creation date
  • modificationdate - ? - last modification date
  • name - character varying(1000) - Given name of the phone
  • providerid - numeric - index into the provider table
  • carrierid - numeric - index into the carrier table
  • neighborhoodid - numeric - index into the neighborhood table
  • bandwidthid - numeric - index into the bandwidth table
  • statusid - numeric - index into the status table
  • certificationid - numeric - index into the certification table
  • comments - character varying(2000) - notes on the node
  • latitude - character varying(200) - latitude
  • longitude - character varying(200) - long
  • altitude - ? - meters above sealevel
  • essid - character varying(500) - essid in use (this should index into a table for multiple radios)
  • channel - character varying(2) (parrt of the radio channel)
  • address - character varying(200) - (this should be an index into an address table)
  • city - character varying(100)
  • state - character varying(100)
  • zip - character varying(20)
  • url_sitephoto - character varying(300) - profile shot to include in a profile page
  • url_information - character varying(300) - Information about the site
  • maintainer1 - character varying(20) - index into user table (change into a new table to support N photos)
  • maintainer2 - character varying(20) -
  • owner - character varying(20) - index into the user table
  • node_geom - geometry - ?
  • pri_pub_ip_block - character varying(128) - IP block provided by DHCP. this would be part of the radio/interface specification.

Check constraints: BR

  • "$1" (srid(node_geom) = -1) BR "$2" ((geometrytype(node_geom) = 'POINT'::text) OR (node_geom IS NULL)) BR

Addresses Table BR Description Street address of users, nodes, etc.

Table Relationships

  • N......N User Table
  • N......N Node Table

Fields BR

General table design notes:

When indexing one table to another table, ie when table A contains a foreign key from table B, follow these practices: BR

  • 1..1 one to one mapping between table A and table B - create a field in table A with attribute 'REFERENCES B'. this tell postgresql that the field contains a unique ID into table B.
  • 1..N one to many mapping between table A and table B - Create a table called A_B with two fields. One field is a uniqueID for table A. the other field is uniqueID for B with attribute 'REFERENCES B'. Create a field in table A with attribute 'REFERENCES A_B'. This will support cascading deletes since there is a path from a record in table A to the corresponding record(s) record in B.
  • All tables should have an 'id' field of type numeric and attribute PRIMARY KEY. most tables should have a creationdate and modificationdate field of type timestamp - the default time is 'without timezone', all stored times should be in GMT.
  • To make a major change to a table, run 'pg_dump -t tablename databasename > file.sql' to get a record of the existing schema. Then modify and run through psql to recreate the table. Note that this method does not seem to save the PRIMARY KEY attribute so be sure to put that back into the id field.

System for getting at the data


  • PHP page talking to a MySQL server

[http://www.nodedb.com/ndbcom.dtd] NodeDB.com xml schema...


CommunityDatabase (last edited 2012-03-11 02:06:36 by RussellSenior)