BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Table Design question for pro          BorlandTalk.com Forum Index -> Delphi Databases (ADO) View previous topic :: View next topic   Author Message Feza Guest Posted: Thu Feb 19, 2004 11:29 am    Post subject: Table Design question for pro Hi, i need to establish database tables to store resident information in a country on the basis of building,floor and flat.i have to store each resident in flats in which there are more than 3 or 4 resident live.Furthermore, Each Floor comprise of 2 or 3 flat.in the same way, Building consist of many floors. Now i need to query, 1.How many floors in each building 2.How many residents live in a building 3.how many flats does a spesific building contain? ideas and expirences are appreciated... Regards FEZA Back to top Feza Guest Posted: Thu Feb 19, 2004 11:38 am    Post subject: Re: Table Design question for pro i forgot to say that i am using Msaccess table with standart ado components. Regards FEZA Back to top Brian Hollister Guest Posted: Thu Feb 19, 2004 3:15 pm    Post subject: Re: Table Design question for pro "Feza" <------> wrote Quote: i forgot to say that i am using Msaccess table with standart ado components. Regards FEZA Start simple. Using the information you have given us, I would start my design phase by examining the four entities you have already mentioned; Building, Floor, Flat, and Resident. I would say these are good candidates for tables. Each one of these will have unique information about them so that information should go into that table and all you need is a way to relate the information together. For instance, a floor must be in a building, there's one relation. A flat must be on a floor, another relation. A Resident must be in a Flat, another relation. Hope that helps you get started, Brian Back to top Kursat Guest Posted: Thu Feb 19, 2004 3:26 pm    Post subject: Re: Table Design question for pro You can create a DB structure like this : Table1--> Buildings BuildingNo (PrimaryKey, Unique) BuildingName Table2--> Floors FloorID (PrimaryKey, AutoIncrement) FloorNo BuildingNo Table3--> Flats FlatNo FloorID ResidentCount --------------------------------------------------------- 1. How many floors in each building : SELECT buildingno, count(FloorID) as floorcounts from floors group by buildingno 2. How many residents live in a building : SELECT sum(fla.ResidentCount) as residentcounts from flats fla, floors flo where (fla.FloorID = flo.FloorID) and (flo.BuildingNo = 3) 3.how many flats does a spesific building contain : SELECT count(flo.FloorID) as floorcounts from floors flo where (flo.BuildingNo =4) Back to top Feza Guest Posted: Thu Feb 19, 2004 3:40 pm    Post subject: Re: Table Design question for pro Quote: Start simple. Using the information you have given us, I would start my design phase by examining the four entities you have already mentioned; Building, Floor, Flat, and Resident. I would say these are good candidates for tables. Each one of these will have unique information about them so that information should go into that table and all you need is a way to relate the information together. For instance, a floor must be in a building, there's one relation. A flat must be on a floor, another relation. A Resident must be in a Flat, another relation. Hope that helps you get started, Brian That's not simple as it seems i guess Look at my design and please give me your recomendation Brian, BUILDING BFDetails FLOOR FFDetails FLAT FRdetails RESIDENT Bno(Auto) Bno Fno(Auto) Fno FLno (Auto) FLNo Rno(Auto) Bname Fno Fnumber FLno FLnumber Rno Rname Bsize ........ ......... RSurname ....... ........ ......... Rage i have many problem with this design, Jet engine doesn't produce an autonumber as long as data entered in the field.In order to create a new flat under specific building, i need Bno information to save under BFDetails table programmatically.Please give attention above code.How would you use this design with ado? How are you going to make data enterance? How would you design form implementation to let user start adding data? Thanks in advance FEZA Back to top Feza Guest Posted: Thu Feb 19, 2004 5:27 pm    Post subject: Re: Table Design question for pro Quote: Table1--> Buildings BuildingNo (PrimaryKey, Unique) BuildingName Table2--> Floors FloorID (PrimaryKey, AutoIncrement) FloorNo BuildingNo Table3--> Flats FlatNo FloorID ResidentCount --------------------------------------------------------- 1. How many floors in each building : SELECT buildingno, count(FloorID) as floorcounts from floors group by buildingno 2. How many residents live in a building : SELECT sum(fla.ResidentCount) as residentcounts from flats fla, floors flo where (fla.FloorID = flo.FloorID) and (flo.BuildingNo = 3) 3.how many flats does a spesific building contain : SELECT count(flo.FloorID) as floorcounts from floors flo where (flo.BuildingNo =4) Thank you Kursad indeed, i do not why i could' figure it out.Northwind database under msaccess confused my mind i think, Because it follows such a schema Regards FEZA Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (ADO) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.