Table / query Design

[sam]

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?

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

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.

 

[sam]

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.

 

[Duane Hookom]

Don't shy away from good, normalized table structures. 100,000 records would
not be an issue if your table is properly indexed.

 

[Van T. Dinh]

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

 

[Van T. Dinh]

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?

 

[sam]

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.

 

[Duane Hookom]

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.