Topic awaiting preservation: database architecture (Page 1 of 1)

OK. I have been contracted by a client to build a database for his website. My client makes a product > sells to dealers in US > dealers sell directly to customers. Purpose of dbase is to allow customers to search for dealers that sell product in their area. Client is interested in ability to search by zip code or by state. The host he currently uses has MySQL functionality- with a GUI interface built in PHP- called PhpMyAdmin. From this GUI interface I can execute MySQL commands. I am not very familiar with MySQL but willing to learn and the interface will definitely make it easier verses coding from a command line.

Now before I am even attempting to delve into PhpMyAdmin- I first want to design the table structure, and am very interested in what you folks think. This is what I came up withso far . . . looking at it logically, I guess it makes sense to do #2, but that?s why I am asking you!

Side notes:
-My understanding is that the primary key is the unique identifier in each table and in order to link 2 or more tables- they have to have the same primary key. Is this correct?
-What does the State and Zip in each table have to be? Secondary keys?
- For idea #2, if it?s a search by state, of course I do not want the extra zip code column to appear in results. Same for searching by zip- I do not want that extra state column to appear in search results.

Thank you so much. As you can see I am just learning and am very grateful for any advice or suggestions. As for using PhpMyAdmin- I plan on utilizing sourceforge.net but will most likely return and use the Asylum. J

CRO8

I would organize the table by dealer!

<BLOCKQUOTE><FONT face="Verdana, Arial">code:</font><HR><pre>
Dealer ID

Ahh. I see.

So you suggest one table, organized by dealer ID (1,2,3,4,5,6. . .) which would be primary key. I can search by any field in table and grab contents of entire row, later on if I choose - connect multiple tables by dealer ID.

Simple enough.

Thanks.
CRO8

ps- How is a secondary key useful? An example with my table would be helpful

I could be wrong (I'm no db expert) but I think 'secondary key' is a generic term for all those fields which aren't primary keys.
I've never actually used the term 'secondary key' before.
Hmmm.....

Yeah, if you organize by dealers, then in the future you have the flexibilty of adding more tables, more information for those dealers. The way I look at it, states and zip codes aren't what's really important. Your client doesn't/won't want to know all sorts of information about particular locations...s/he wants to know things about dealers. Make that your focus and you'll be good.

gracias

key's create indexes for increased performance. They speed up searches by marking what data is needed for the search most often.



.:[ Never resist a perfect moment ]:.

As far as I can remember, the principal difference between a primary and a secondary index is that the primary index must contain a unique value for each record, whereas a secondary index is allowed to contain duplicate entries.

On whether to index or not, the cruncher is in fact, the anticipated size of your database. If you only expect to have a few hundred records, then select x where field=value won't be much quicker if the field is indexed vs not indexed (at least, not noticably so to a human being). However, if it's a big database, there's definitely a speed benefit from indexing.

Index searching is efficient, being typically based on some sort of a balanced-tree structure, resulting in a minimum number of hops to find an entry. OTOH, searching on an unindexed field is sequential, so the DBMS has to plod through every record, reading the values of all the fields, and applying the test to the relevant field. This is one of the penalties you pay for having variable-length records. The system doesn't know where the field is located in any record until it reads the record data.

As far as maintaining ordered indexes, maintenance overhead is fairly low, and, more importantly, irrelevant in most cases, given that the data is only being written occasionally, and at human input speeds. However it is being read often - and at as near machine speeds as possible. So access speed is the important factor in most web-oriented database applications.

Rule of thumb - if it's a field you expect to use often as a means of accessing or ordering your data - index it. If it's a field which will often be used in combimnation with other ones for ordering or searching data - create an additional key out of the combined values ... the keyword here is OFTEN ... and since it looks as though you'll be writing a fairly static set of queries, my recommendation would be to index everything you expect to use as a search criterion in a pre-defined report. The ones that come immediately to mind are Dealer ID, Dealer Name, Zip and State. Dealer ID is the obvious primary, unique key. This can then be used to index and access sales data, prospect reports, etc. etc. when they are created.

... enjoy the challenge.

I'd recommend going to PHPBuilder.com and checking out their articles on database normalization.

Doing it in a single table format is easiest to navigate however by looking at it it's not very flexible for future additions. What if they want multiple emails listed? What if 20 of the phone numbers are the same? Do you really want to duplicate all that data when a relational table will save a lot of space?

It all depends how complex you want to get. Personally I never define more than 1 index and that index is usually an auto-incrementing field that has absolutely nothing to do with the data. Unless you are dealing with hundreds of thousands of records you won't see a significant hit to performance by only using 1 index.

JMJimmy

Been away for bit. Thanks for the response.

I will do some more research in building efficient databases and will report back!

Thanks.
CRO8