Closed Thread Icon

Topic awaiting preservation: database architecture (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12454" title="Pages that link to Topic awaiting preservation: database architecture (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: database architecture <span class="small">(Page 1 of 1)</span>\

 
CRO8
Bipolar (III) Inmate

From: New York City
Insane since: Jul 2000

posted posted 09-28-2002 19:24

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

mobrul
Bipolar (III) Inmate

From:
Insane since: Aug 2000

posted posted 09-28-2002 19:56

I would organize the table by dealer!

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

CRO8
Bipolar (III) Inmate

From: New York City
Insane since: Jul 2000

posted posted 09-28-2002 20:30

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

mobrul
Bipolar (III) Inmate

From:
Insane since: Aug 2000

posted posted 09-30-2002 17:26

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.

CRO8
Bipolar (III) Inmate

From: New York City
Insane since: Jul 2000

posted posted 10-01-2002 23:39

gracias

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 10-01-2002 23:48

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 ]:.

trib
Paranoid (IV) Inmate

From: Den Haag, Netherlands
Insane since: Sep 2002

posted posted 10-02-2002 10:26

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.




JMJimmy
Bipolar (III) Inmate

From: Picton, Ontario, Canada
Insane since: Apr 2001

posted posted 10-02-2002 14:10

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

CRO8
Bipolar (III) Inmate

From: New York City
Insane since: Jul 2000

posted posted 10-04-2002 20:23

Been away for bit. Thanks for the response.

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

Thanks.
CRO8

« BackwardsOnwards »

Show Forum Drop Down Menu