|
|
Posted by Tyno Gendo on 04/07/07 15:50
Mr. Newt wrote:
> "Tyno Gendo" <user@example.com> wrote in message
> I may be making my life harder than it needs to be. I'm writing a db for a
> friend and have never done anything this big before. So, trying to be
> fancy, I decided to try normalizing the data first. I decided to separate
> the advertiser table from the address table, because I might add addresses
> (like mailing addresses of customers) to the address table. So, that's why
> I want to be able to access/write data in more than one table at a time. I
> figured it can't be that hard, because every php example I've seen has
> multiple tables and data must be related.
>
> Anything I can read or direction in this area would be appreciated.
>
> Thank you.
>
> Robert
Well, if the address is related to the advertiser then you will first
want to INSERT the advertiser to get a unique ID for the advertiser,
then in the address table you will want to INSERT also including the ID
you get as a foreign key, so you can link between the two in future.
First execute the first query to insert advertiser, then call
mysql_insert_id() to get the inserted rows Primary KEY:
mysql_query(<new_advertiser_insert_statement>);
$newid = mysql_insert_id();
You should have a field set aside in your advertiser table which is an
autonumber, primary key, for example 'pkAdvertiserID' and in the address
table a similar field, call it fkAdvertiserID for foreign key to
advertiser table, type INT (but not auto_number in this table).
so..
[ADVERTISER_TABLE]
pkAdvertiserID INT AUTO_INCREMENT PRIMARY_KEY
....
....
[ADDRESS_TABLE]
pkAddressID INT AUTO_INCREMENT PRIMARY KEY
fkAdvertiserID INT NOT NULL
....
....
Be honest with you though, unless you have more than one advertiser with
the same address, you'd be better just keeping the address in the
advertiser table.
Same with customers, they are perhaps unlikely to have more than one
address? And if they do you probably want to have separate tables,
so.. customer table and customer_address table, like you have advertiser
table and advertiser_address table.
You will obviously know best though as you know the scope of the project :-D
Navigation:
[Reply to this message]
|