If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table / query Design
Hi,
I'm wanting to set this up but am unsure of the best way to go about it. All I have is basically a 'service' which will belong to particular areas i.e. service a, suburb a, suburb b, suburb c etc. Service b, suburb a, suburb d, suburb n etc. Each service could be associated with 30 or more different suburbs. What i will be required to do is be able to enter a service and a suburb and then be given a match. Just after some ideas on the best way to set it up as I'm worried about the speed of it. There will be 1000 or so services each with 30 or more suburbs against each one. I could set it up so the suburbs were listed as a string and then just do a like*suburb* or set it up properly using tables. What method would be fastest? |
#2
|
|||
|
|||
Table / query Design
See comments in-line.
-- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, I'm wanting to set this up but am unsure of the best way to go about it. All I have is basically a 'service' which will belong to particular areas i.e. service a, suburb a, suburb b, suburb c etc. Service b, suburb a, suburb d, suburb n etc. Beware if you create Records as above, you are moving in the direction of Excel, NOT database. Since each Service can be associated with 30 or MORE Suburbs, how many Fields would you create in your Table? Note that properly normalised Tables tend to be narrow (small number of Fields). Each service could be associated with 30 or more different suburbs. What i will be required to do is be able to enter a service and a suburb and then be given a match. Just after some ideas on the best way to set it up as I'm worried about the speed of it. There will be 1000 or so services each with 30 or more suburbs against each one. I could set it up so the suburbs were listed as a string and then just do a like*suburb* or set it up properly using tables. No. The list of Suburbs violates the First Normal Form of database design. What method would be fastest? Can't say. I design databases with proper structures, NOT database structure that fastest to do. However, the proper way for you to proceed is to read up on Relational Database Design Theory and the structure should be clear to you. |
#3
|
|||
|
|||
Table / query Design
Hi, Thanks for you input. I understand how it should be done but I'm thinking
1000 * 30 would give a potential of 30000 records to go through on each search. I'm not sure you followed on the second, it would be two fields only, provider and suburb. Suburb would hold suburb1, suburb 2, suburb3 etc. exactly as shown. This would make it wide but 20000 shorter. "Van T. Dinh" wrote: See comments in-line. -- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, I'm wanting to set this up but am unsure of the best way to go about it. All I have is basically a 'service' which will belong to particular areas i.e. service a, suburb a, suburb b, suburb c etc. Service b, suburb a, suburb d, suburb n etc. Beware if you create Records as above, you are moving in the direction of Excel, NOT database. Since each Service can be associated with 30 or MORE Suburbs, how many Fields would you create in your Table? Note that properly normalised Tables tend to be narrow (small number of Fields). Each service could be associated with 30 or more different suburbs. What i will be required to do is be able to enter a service and a suburb and then be given a match. Just after some ideas on the best way to set it up as I'm worried about the speed of it. There will be 1000 or so services each with 30 or more suburbs against each one. I could set it up so the suburbs were listed as a string and then just do a like*suburb* or set it up properly using tables. No. The list of Suburbs violates the First Normal Form of database design. What method would be fastest? Can't say. I design databases with proper structures, NOT database structure that fastest to do. However, the proper way for you to proceed is to read up on Relational Database Design Theory and the structure should be clear to you. |
#4
|
|||
|
|||
Table / query Design
Don't shy away from good, normalized table structures. 100,000 records would
not be an issue if your table is properly indexed. -- Duane Hookom MS Access MVP "sam" wrote in message ... Hi, Thanks for you input. I understand how it should be done but I'm thinking 1000 * 30 would give a potential of 30000 records to go through on each search. I'm not sure you followed on the second, it would be two fields only, provider and suburb. Suburb would hold suburb1, suburb 2, suburb3 etc. exactly as shown. This would make it wide but 20000 shorter. "Van T. Dinh" wrote: See comments in-line. -- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, I'm wanting to set this up but am unsure of the best way to go about it. All I have is basically a 'service' which will belong to particular areas i.e. service a, suburb a, suburb b, suburb c etc. Service b, suburb a, suburb d, suburb n etc. Beware if you create Records as above, you are moving in the direction of Excel, NOT database. Since each Service can be associated with 30 or MORE Suburbs, how many Fields would you create in your Table? Note that properly normalised Tables tend to be narrow (small number of Fields). Each service could be associated with 30 or more different suburbs. What i will be required to do is be able to enter a service and a suburb and then be given a match. Just after some ideas on the best way to set it up as I'm worried about the speed of it. There will be 1000 or so services each with 30 or more suburbs against each one. I could set it up so the suburbs were listed as a string and then just do a like*suburb* or set it up properly using tables. No. The list of Suburbs violates the First Normal Form of database design. What method would be fastest? Can't say. I design databases with proper structures, NOT database structure that fastest to do. However, the proper way for you to proceed is to read up on Relational Database Design Theory and the structure should be clear to you. |
#5
|
|||
|
|||
Table / query Design
To be correct, it is 29000 shorter but the Table is still incorrectly
designed. Note that JET is designed to work efficently with properly designed Table Structure, NOT short Tables. Ditto per Duane -- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, Thanks for you input. I understand how it should be done but I'm thinking 1000 * 30 would give a potential of 30000 records to go through on each search. I'm not sure you followed on the second, it would be two fields only, provider and suburb. Suburb would hold suburb1, suburb 2, suburb3 etc. exactly as shown. This would make it wide but 20000 shorter. |
#6
|
|||
|
|||
Table / query Design
BTW, on the second set-up with 2 Fields only with the lists in the Field
values, I wouldn't have written what I wrote without understanding what you described ... Have you checked out what the First Normal Form is? -- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, Thanks for you input. I understand how it should be done but I'm thinking 1000 * 30 would give a potential of 30000 records to go through on each search. I'm not sure you followed on the second, it would be two fields only, provider and suburb. Suburb would hold suburb1, suburb 2, suburb3 etc. exactly as shown. This would make it wide but 20000 shorter. |
#7
|
|||
|
|||
Table / query Design
Thanks to both of you for your input. I know what proper design is,
normalisation etc. etc. I ALWAYS stick with proper design but i thought maybe this time i might have to stray but thanks for getting me back on the straight and narrow. One of the main reasons i was hoping someone would say it would be ok or at least make me think it wasn't such a bad thing is because i have no idea how I'm going to normalise it without many many hrs of work. I have inherited the dbase which is set up similar to how i explained ie. suburb1 suburb2 suburb3 etc. 1000 odd records with 30 or so suburbs with each one seperated only by spaces. Not an easy task to seperate when suburbs have two and three names such as fern tree gully also with spaces. but anyways.... i'll do it properly. "Van T. Dinh" wrote: BTW, on the second set-up with 2 Fields only with the lists in the Field values, I wouldn't have written what I wrote without understanding what you described ... Have you checked out what the First Normal Form is? -- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, Thanks for you input. I understand how it should be done but I'm thinking 1000 * 30 would give a potential of 30000 records to go through on each search. I'm not sure you followed on the second, it would be two fields only, provider and suburb. Suburb would hold suburb1, suburb 2, suburb3 etc. exactly as shown. This would make it wide but 20000 shorter. |
#8
|
|||
|
|||
Table / query Design
This may not be quite as difficult as you think. Consider the employee table
in the Employees table in Northwind.mdb. There is a field [FirstName]. Assume you have a table tblSeveralNames with fields [ID] Primary Key [SeveralNames] Text field with values of first names separated by spaces The field SeveralNames will contain values like SeveralNames ================= Nancy Andrew Steven Anne Michael Laura Janet Margaret Robert Now, you want to create a table of normalized records with combinations of EmployeeID from the Employees table and ID from the tblSeveralNames table. Create a query with the sql of: SELECT Employees.EmployeeID, tblSeveralNames.ID, Employees.FirstName, tblSeveralNames.SeveralNames FROM Employees, tblSeveralNames WHERE (((tblSeveralNames.SeveralNames) Like "*" & [FirstName] & "*")) ORDER BY tblSeveralNames.ID; You can use this query to create a new table. -- Duane Hookom MS Access MVP "sam" wrote in message ... Thanks to both of you for your input. I know what proper design is, normalisation etc. etc. I ALWAYS stick with proper design but i thought maybe this time i might have to stray but thanks for getting me back on the straight and narrow. One of the main reasons i was hoping someone would say it would be ok or at least make me think it wasn't such a bad thing is because i have no idea how I'm going to normalise it without many many hrs of work. I have inherited the dbase which is set up similar to how i explained ie. suburb1 suburb2 suburb3 etc. 1000 odd records with 30 or so suburbs with each one seperated only by spaces. Not an easy task to seperate when suburbs have two and three names such as fern tree gully also with spaces. but anyways.... i'll do it properly. "Van T. Dinh" wrote: BTW, on the second set-up with 2 Fields only with the lists in the Field values, I wouldn't have written what I wrote without understanding what you described ... Have you checked out what the First Normal Form is? -- HTH Van T. Dinh MVP (Access) "sam" wrote in message ... Hi, Thanks for you input. I understand how it should be done but I'm thinking 1000 * 30 would give a potential of 30000 records to go through on each search. I'm not sure you followed on the second, it would be two fields only, provider and suburb. Suburb would hold suburb1, suburb 2, suburb3 etc. exactly as shown. This would make it wide but 20000 shorter. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |
surely a form with a ListBox can be used in a query? | 1.156 | Running & Setting Up Queries | 14 | June 2nd, 2004 04:54 PM |
Table design | BillT | New Users | 11 | May 25th, 2004 03:41 PM |
Table design for a booking system | Brian C | Database Design | 2 | April 27th, 2004 03:11 AM |