I'm a relative newbie so forgive my ignorance.

I'm setting up a site in DreamweaverMX using Apache, php and MySQL.

I'm just creating a HTML form for users to enter data, and came across a problem.

I am wondering how I submit data into 2 tables at once. What I mean is that one table may have a column that references the foreign key of the other table. Only the table that refereces the column of the other table will have data entered into it. I think I may have just answered my own question.

Does anyone know of a site that you can submit you database structure, and people can review it for errors.

Point me in the right direction please    

try here.

Have you done "third normal form", you can search for the actual process, but I do it so much that I tend to go straight into creating my table without thinking about it.

Basically it removes tables with one to one and many to many relationships. This give a very efficient structure and prevents repetition of information.

Thanks for the reply,

I have gone through the normalisation process, but at the end have a table with 8 foreign keys in it.....

They are all needed to make this table have all the relevant information...

Is this too many foreign keys in one table

As I said users will choose an option from a drop down box that will essentially be a foreign key of a table, that will enter a value into a row of my main table.......

make any sense

You can have as many foreign keys as you need, it doesnt really matter.

I have a database of an estate agents properties. It has keys linking to the different features the home has, which are kept in other tables. This makes it easier to add in new features.
ie type of parking (none,forecourt,garage, double garage). We can add in triple garage, if we ever came across such a property, or change garage to single garage with the minimum of fuss as it only appears once in the database, and its referenced everywhere else.

We did contemplate on the next version to reduce the tables down to the main data and a single option table. This would make handling the data easier as we wouldn't have to determine which table to get the data from.

TABLE parking, location, plumbing, cooker_points .....etc..
 ID
 type

would become just 2 tables

TABLE options
  Option_ID
  Section_id
  Description

TABLE sections
  Section_ID
  Section_Name

We could then have a property with main details and a seperate table of associated features. This would allow as many options as is required

Show the structure here so we can see, and confirm how efficient it is.

I have a database for selling houses privately on my site.

There are 8 tables that constitute the foreign keys of the main table. These are:

Bedrooms_id
Construction_id
Contact_id
Countries_id
Detailed area_id
property details_id
region_id
state_id

And all of these tables appear as foreign keys in my main table that has data entered into it by the user. Other than this info the main table has columns on:
price
town
address
property_id

This is the first relational database I have put together, and it is only in it's infancy, so any comments would be appreciated

There is absolutely nothing wrong with your database structure.
You may want to include some text areas to describe the property as well.

I didn't design the original database(I would have done it differently), but here is the base information. It stores Renting and Selling property. Everything, except PropertyID, ending in ID is a foreign key.

CREATE TABLE prop_property (
 PropertyID int(11) NOT NULL auto_increment,
 HouseNumber varchar(14) default NULL,
 Address tinytext,
 PostCodeDistrict varchar(4) default NULL,
 PostCodeArea char(3) default NULL,
 BedroomsID int(11) NOT NULL default '1',
 TypeID int(11) NOT NULL default '1',
 PropertyAgeID int(11) NOT NULL default '1',
 ConditionID int(11) NOT NULL default '1',
 TaxBandID char(1) NOT NULL default 'N',
 LocationID int(11) NOT NULL default '1',
 Renting tinyint(4) default NULL,
 RentID int(11) NOT NULL default '1',
 RentPCM decimal(13,2) default NULL,
 AvailDate date default NULL,
 HoldID int(11) NOT NULL default '1',
 LeaseLength int(3) NOT NULL default '0',
 PropertyValue decimal(13,2) default NULL,
 Phone tinyint(4) NOT NULL default '0',
 WindowID int(11) NOT NULL default '1',
 HeatingID int(11) NOT NULL default '1',
 KitchenID int(11) NOT NULL default '1',
 FurnishedID int(11) NOT NULL default '1',
 GarageID tinyint(4) NOT NULL default '1',
 GardenID tinyint(4) NOT NULL default '1',
 CookerPointsID int(11) NOT NULL default '1',
 PlumbingID int(11) NOT NULL default '1',
 Cooker tinyint(4) default NULL,
 Tumbledryer tinyint(4) default NULL,
 Dishwasher tinyint(4) default NULL,
 WashingMachine tinyint(4) default NULL,
 ColdStorageID int(11) NOT NULL default '1',
 Microwave tinyint(4) default NULL,
 BusRoute tinyint(4) default NULL,
 BusDetails varchar(50) NOT NULL default 'N/A',
 RailRoute tinyint(4) default NULL,
 RailDetails varchar(50) NOT NULL default 'N/A',
 TubeRoute tinyint(4) default NULL,
 TubeDetails varchar(50) NOT NULL default 'N/A',
 MotorwayAccess tinyint(4) default NULL,
 MotorwayDetails varchar(50) NOT NULL default 'N/A',
 Alarm tinyint(4) default NULL,
 AlarmDetails varchar(50) NOT NULL default 'N/A',
 BedroomDetails text,
 KitchenDetails text,
 BathroomID int(11) NOT NULL default '1',
 BathroomDetails text,
 Room1ID int(11) NOT NULL default '1',
 Room1 text,
 Room2ID int(11) NOT NULL default '1',
 Room2 text,
 Room3ID int(11) NOT NULL default '1',
 Room3 text,
 PropertyHeader varchar(40) default NULL,
 PropertyDescription text,
 AgentID int(11) default NULL,
 DateAmended timestamp(14) NOT NULL,
 DateCreated timestamp(14) NOT NULL,
 Enquiries int(11) NOT NULL default '0',
 HitCount int(11) NOT NULL default '0',
 Pets tinyint(4) default NULL,
 Smokers tinyint(4) default NULL,
 Children tinyint(4) default NULL,
 DSS tinyint(4) default NULL,
 HouseShare tinyint(4) default NULL,
 Proofed tinyint(4) NOT NULL default '2',
 PRIMARY KEY  (PropertyID)
) TYPE=MyISAM;

The website is http://www.rentalsandsales.co.uk/, which is not too bad. I did the backend for this and re-did the style on the front. I dont like the way the search works but then again I didnt write that bit. There are a few properties in Brighton (SE, Brighton & Hove, Brighton)


I can't let you enter the admin area, but you can see what it does through the manual http://admin.rentalsandsales.co.uk/manual.pdf

p.s. I also did the maps, it took me about 6 months.

Thanks for the help, it's good to know that my table structure is not totally wrong.

I'll have a look at the website to give me a few ideas.

Cheers

A quick note to welcome you both to the board. I'm usually more active that I have been in the last day or two, but I've got mobile phone only access for a few days. Delighted to see questions and answers flowing - thank you!

Graham

Its Gear28 here again.........

Just checking........ so the database you described above is made up of many small tables, and then one big table that contains all of the foreign keys??

Or do you have a few tables that contain some of the foreign keys....

thanks for the help

Yup all the field, in the large table above, that end with ID are foreign keys to other small tables.

ie. this is a dump of the garage table:-

CREATE TABLE prop_garage (
 GarageID int(11) NOT NULL auto_increment,
 Garage varchar(40) NOT NULL default '',
 icon varchar(255) NOT NULL default 'garage.gif',
 PRIMARY KEY  (GarageID)
) TYPE=MyISAM;

#
# Dumping data for table `prop_garage`
#

INSERT INTO prop_garage VALUES (1, 'N/A', 'garage.gif');
INSERT INTO prop_garage VALUES (2, 'Garage', 'garage.gif');
INSERT INTO prop_garage VALUES (3, 'Driveway', 'garage.gif');
INSERT INTO prop_garage VALUES (4, 'Garage / Driveway', 'garage.gif');
INSERT INTO prop_garage VALUES (5, 'Off-Street Parking', 'garage.gif');
INSERT INTO prop_garage VALUES (6, 'Parking spaces', 'garage.gif');

---