List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:May 30 2002 9:24pm
Subject:Re: first mysql table; syntax qustion
View as plain text  
Hi.

On Tue, May 28, 2002 at 04:32:20PM -0700, jcunningham@stripped wrote:
> Thanks for the reply!  Should I change the structure such that all
> varchar fields are in the same table?

I see no reason for this. It may make a (minor) speed difference, but
you should only ever change your table layout due to performance, if
you already know, that it will matter. If you are not sure, leave it
as it is and change it when you somewhen should encounter the
bottleneck.

> I believe you did understand my design correctly. 

Well, I did not, I think. :-)

> Available_properties contains the primary data.  Property names is
> just a table that contains subsequent data for available_properties
> and details will be a link from the properties.php page to
> properties/details.php I added the other two keys as columns to
> available_properties.  Let me know if I should restructure anything.

I recommend you forget the notion to structure the database according
your php scripts, if this is what you did. The structure of your
tables may or may not relate directly to the structure of your web
pages, depending on the application, but there is no general
requirement for this. Therefore you should design the DB layout quite
independendly.

If I understood correctly, there is a 1-to-1 relation between all the
tables and details and property_names only fill in further attributes
to the available_properties? If so, I see no structural reason to
devide the data into 3 tables. You could put them into one as well. It
doesn't really hurt to put them in 3 tables, but there is also no
advantage in doing so, except for better readability for humans.

May I suggest you to read a bit about normalization (in database
theory - see the manual for suggestion). It defines what should or
should not be in one table.

If I missed your point, I suggest that you provide some example data
for the tables.

Bye,

	Benjamin.

> mysql> show tables;
> +----------------------+
> | Tables_in_properties |
> +----------------------+
> | available_properties |
> | details              |
> | property_names       |
> +----------------------+
> 3 rows in set (0.00 sec)
> 
> mysql> desc available_properties;
> +-----------------+--------------+------+-----+---------+---------------
> -+
> | Field           | Type         | Null | Key | Default | Extra
> |
> +-----------------+--------------+------+-----+---------+---------------
> -+
> | av_pro_id       | int(11)      |      | PRI | NULL    | auto_increment
> |
> | property_name   | varchar(50)  |      |     |         |
> |
> | type            | varchar(50)  | YES  |     | NULL    |
> |
> | city            | varchar(25)  | YES  |     | NULL    |
> |
> | description     | varchar(125) | YES  |     | NULL    |
> |
> | number_of_units | varchar(25)  | YES  |     | NULL    |
> |
> | pro_id          | int(11)      |      |     | 0       |
> |
> | d_id            | int(11)      |      |     | 0       |
> |
> +-----------------+--------------+------+-----+---------+---------------
> -+
> 8 rows in set (0.00 sec)
> 
> mysql> desc property_names;
> +---------------+--------------+------+-----+---------+----------------+
> | Field         | Type         | Null | Key | Default | Extra          |
> +---------------+--------------+------+-----+---------+----------------+
> | pro_id        | int(11)      |      | PRI | NULL    | auto_increment |
> | property_name | varchar(50)  |      |     |         |                |
> | address       | varchar(100) | YES  |     | NULL    |                |
> | cross_street  | varchar(50)  | YES  |     | NULL    |                |
> +---------------+--------------+------+-----+---------+----------------+
> 4 rows in set (0.00 sec)
> 
> mysql> desc details;
> +-------------+-------------+------+-----+---------+----------------+
> | Field       | Type        | Null | Key | Default | Extra          |
> +-------------+-------------+------+-----+---------+----------------+
> | d_id        | int(11)     |      | PRI | NULL    | auto_increment |
> | type        | varchar(50) | YES  |     | NULL    |                |
> | price       | smallint(6) | YES  |     | NULL    |                |
> | floor       | varchar(15) | YES  |     | NULL    |                |
> | sq_ft       | smallint(6) | YES  |     | NULL    |                |
> | suite       | smallint(6) | YES  |     | NULL    |                |
> | description | text        | YES  |     | NULL    |                |
> | available   | varchar(15) | YES  |     | NULL    |                |
> +-------------+-------------+------+-----+---------+----------------+
> 8 rows in set (0.00 sec)
[...]

-- 
benjamin-mysql@stripped
Thread
first mysql table; syntax qustionjustin cunningham28 May
  • Re: first mysql table; syntax qustionBenjamin Pflugmann29 May
    • Tables and IndexesMarcel29 May
    • RE: first mysql table; syntax qustionKevin Carlson29 May
      • Re: first mysql table; syntax qustionBenjamin Pflugmann30 May
    • RE: first mysql table; syntax qustionjustin cunningham29 May
      • Re: first mysql table; syntax qustionBenjamin Pflugmann30 May