Sydcomebak Posted July 4, 2008 Share Posted July 4, 2008 I'm making a database for a gated community. I work here as a security guard, and currently the "database" is a 3-inch binder with each home having a page where the residents tell us which people and companies are allowed in, and when, and why. VIEW! http://www.sydcomebak.com/misc/freq_entry.jpg[/img] I want the displayed electronic version to look like this: http://www.sydcomebak.com/cambridge_place/name_lists/name.html So what would be a good set of table structures to normalize all of this? Here's where I've already done: HouseTblPeopleTblResidentTbl -HouseID (unique)-NameID (unique)-ResidentID (unique) -HouseNum-NameLast-Name_ID -HouseStreet-NameFirst-House_ID -HouseCombined-etc. Where else should I go with this? My thoughts: -A table of all companies that visit or have visited the community -A table of "family" similar to the resident table where people are linked to homes -A table of "friends&employees" as above -A table of "company_visits" as above -A log of all entries with date & Time stamps recording who went where when Phase1: - Replace the binder with an electronic version of all current info Phase2: - Add the ability to add and remove items from the binder Phase3: - Log all entries to the community as they happen by clicking on people in lists. Phase4: - The eventual goal is to have residents be able to log on and edit their own lists on the web. I know I've laid a lot out there, but I'm trying to make sure that I'm not going down the wrong path before it's gone to far. Quote Link to comment Share on other sites More sharing options...
interpim Posted July 4, 2008 Share Posted July 4, 2008 I would recommend you google for some beginner PHP tutorials, a lot of them will go over how to insert and remove things from a database. As far as your requirements go for the software goes, I don't know if you are going to need that many tables. If you have any specific questions in the development process post back with those specific questions. We can help you with code here, but we will not write it for you. Quote Link to comment Share on other sites More sharing options...
Sydcomebak Posted July 4, 2008 Author Share Posted July 4, 2008 I'm chugging through the code one function at a time. You can see my work developing here: www.sydcomebak.com/cambridge_place/ I wasn't looking for anyone to pass me a lick of code on this thread, just someone to look at my ideas and brainstorm with me. Quote Link to comment Share on other sites More sharing options...
interpim Posted July 4, 2008 Share Posted July 4, 2008 It looks like you have a decent start... I didn't see any forms for data entry or editing, but I wouldn't add that unless you have a login method to prevent vandalism. Quote Link to comment Share on other sites More sharing options...
Sydcomebak Posted July 4, 2008 Author Share Posted July 4, 2008 Once I've decided on the format, -I'll add the tables --Then I'll add the add/remove functions with some test data ---Then I'll populate a local database with all real info ----Then I'll add a security measure of some sort -----Then I'll upload the real DB with the live data My first wrench in the gears of my mind: People who work at a residence... Bill Smith is a pool cleaner for several houses - Add him to the people table - Add him to the friends_employees table with job_field=Pool Service John Jones is a pool cleaner for several houses with Beautiful Pool - Add him to the people table - Add his people# to the CompaniesTbl where field_CoName=Beautiful Pool - Add Co# to the CompanyVisitsTbl for this house# I don't know why this seems like more work than it should be... Quote Link to comment Share on other sites More sharing options...
interpim Posted July 4, 2008 Share Posted July 4, 2008 maybe you could build a table of common visitors... essentially you could add them to each persons authorized visitor list the same way you would anyone else, but to save time, you could have a special link to go to a page full of your common visitors much like your pool cleaner example. If you are going to change the people on a regular basis, I would say have a database table that has the following fields... unique_id, house_num, resident, visitor, date_start, date_end, time_start, time_end, reason basically each row of your database could be for each visitor expected. so you could have several rows for a certain house_num to reflect several visitors. the database you are using will be able to extract them individually based on any of the fields. to add your common visitor, you would already have the info. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.