Command line query and insert

IBazz · January 11, 2010, 1:43pm

Hi,

familiarising myself with the phpMyAdmin command line and I need to do a query and insert. Is that possible?

firstly I can get the data I need for the insert.


SELECT business_id
     , address_id 
 from business_details

but how do I then insert ignore into business_addresses with that data? do I need to build a script to process it?


insert ignore into business_addresses (business_id, address_id)
     values (business_id, address_id);

bazz

rpkamp · January 11, 2010, 2:07pm

You can combine the two queries:


INSERT IGNORE INTO
  business_addresses (business_id, address_id)
SELECT
  business_id, address_id
FROM business_details

PS. This query gives me the feeling your database is not in 1NF. Please look into that

r937 · January 11, 2010, 2:34pm

huh? are you referring to the SQL tab? the big textarea form field where you can enter SQL statements?

i’d hardly call that a “command line” – more descriptive would be “SQL window” as other software applications also use this term

IBazz · January 11, 2010, 2:40pm

Thanks ScallioXTX,

Thank you for the pointer about 1NF but; which part of the query suggests I’ve not achieved it? Maybe I have missed something in my effort to achive 3NF.

I did have 1 address per business record, hence the two values in business_details (PK and FK).

Now I am making a m:m table so each business can have one or more address depending on type, eg billing, postal, location, admin, etc. So I have started to fill a table with FKs to business_details and FKs to addresses where there is a table (addresses) and a table (business_details).

The plan is that once I have built and populated the m:m table, I shall remove the 1:1 relationship for addresses from the business_details table

Trying to keep it all normalised here

Please let me know if I still seem to be violating 1NF.

bazz

IBazz · January 11, 2010, 2:41pm

Yup rudy, you got it. OK, sorry for the error in terminology.

bazz

r937 · January 11, 2010, 2:51pm

regarding your m:m business:address structure…

at what time to you insert a new address? do you pre-populate this table with all addresses, and then wait for businesses to link to them? or do you create an address only if at least one business needs it? and how do you detect duplicate addresses? will you delete an address if it no longer has any businesses linked to it? how do you detect this?

there’s a heck of a lot of extra coding required to maintain an address table, you see

what i’m driving at is that many people declare an address table when in fact they really don’t need one at all!!!

the “acid test” is… are you interested in addresses regardless of whether they have any business?

i’ll bet the answer is no

IBazz · January 11, 2010, 3:26pm

Thanks rudy,

when a business is subscribing, they enter the addresses for billing, administrator, location etc as appropriate. (one business - many addresses)

So the addresses are entered to the table, addresses.
The business is entered to business_details

so as not to have duplicate addresses in table addresses, I thought I should have a third table to associate business, addresses and address type.


CREATE TABLE business_addresses
( address_id int(11) NOT NULL
, business_id int(11) NOT NULL
, address_type varchar(32) NOT NULL default 'Location'
, PRIMARY KEY  (address_id,business_id,address_type)
, KEY reverse_ix (business_id,address_id,address_type)
, constraint business_addresses_business_fk
    foreign key (business_id)
      references business_details (business_id) on delete cascade
, constraint business_addresses_address_fk
    foreign key (address_id)
      references addresses(id) on delete cascade
) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_unicode_ci;

The other possibility I thought of was not having the ‘joining table’.

Instead, I could have each address listed and in its record, store what the purpose of the address is to be, eg admin, postal etc. But I moved away from that thinking because a business which has one address for all types (billing, admin etc), would have that same address listed numerous times in the address table where the only difference between one if its records and another, would be the field address_type.

which is more correct? having the joining table or, making the address table like this (where one address may be repeated for each address type):-


CREATE TABLE address
( business_id int(11) NOT NULL
, address_type varchar(32) NOT NULL
, house_name varchar(64) NOT NULL
, house_number varchar(12) NOT NULL
, address_1 varchar(64) NOT NULL
, address_2 varchar(64) NOT NULL
, address_3 varchar(64) NOT NULL
, town varchar(168) NOT NULL
, townland varchar(64) NOT NULL
, county varchar(32) NOT NULL
, city varchar(60) NOT NULL
, post_code varchar(11) NOT NULL
, country_name varchar(90) NOT NULL
, grid varchar(12) NOT NULL
, latitude varchar(12) NOT NULL
, longitude varchar(12) NOT NULL
, northings varchar(6) NOT NULL
, eastings varchar(6) NOT NULL
, PRIMARY KEY  (business_id,address_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 collate utf8_unicode_ci;

bazz

r937 · January 11, 2010, 3:46pm

in my opinion – and that’s the key part of this sentence – you should not have an address table at all

you need:

a businesses table, sans addresses
a business addresses table, with one row per business address

so you have a 1:m relationship, but you do ~not~ have the m:1 relationship to an addresses table

in essence, the business_addresses table is ~like~ the joining table, but instead of joining to the addresses table (which you don’t need), it simply carries the address

please let me know if you don’t understand this before we move on

IBazz · January 11, 2010, 4:22pm

That makes sense. It also seems to seem very similar to this table set up.



CREATE TABLE address
( business_id INT(11) NOT NULL
, address_type VARCHAR(32) NOT NULL
, house_name VARCHAR(64) NOT NULL
, house_number VARCHAR(12) NOT NULL
, address_1 VARCHAR(64) NOT NULL
, address_2 VARCHAR(64) NOT NULL
, address_3 VARCHAR(64) NOT NULL
, town VARCHAR(168) NOT NULL
, townland VARCHAR(64) NOT NULL
, county VARCHAR(32) NOT NULL
, city VARCHAR(60) NOT NULL
, post_code VARCHAR(11) NOT NULL
, country_name VARCHAR(90) NOT NULL
, grid VARCHAR(12) NOT NULL
, latitude VARCHAR(12) NOT NULL
, longitude VARCHAR(12) NOT NULL
, northings VARCHAR(6) NOT NULL
, eastings VARCHAR(6) NOT NULL
, PRIMARY KEY  (business_id,address_type)
, constraint business_address_business_fk
    foreign key (business_id)
      references business_details (business_id) on delete cascade
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

or does that statement mean I don’t actually understand you?

bazz

r937 · January 11, 2010, 4:34pm

actually, yes, you do understand

there remains then only the issue of how to indicate that a single address fulfills multiple roles (e.g. billing, admin etc.)

IBazz · January 11, 2010, 4:37pm

heh! thats why I thought the m:m table would be suitable.injured:

Mmmm, maybe we need a m:m between addresses and address_types? spur of the moment thought I’ll try to think it through.

bazz

r937 · January 11, 2010, 4:42pm

well, as this is mysql, you could use the SET datatype

normally i would not recommend it (its evilness approaches that of the ENUM)

you want as few rows in this table as necessary (i.e. only distinct addresses per business) without the added complexity of an additional table

naturally, SET introduces additional complexities, but you would only need it when returning all rows for a given business

IBazz · January 11, 2010, 5:38pm

Reading up on SET and I don’t like the part which shows the db will no longer be normalised.

So, if I did make a joining table to relate addresses to each address_type, do you know of any inefficiencies or complications that could/would arise from that?

Already, I can ‘see’ how querying for a business postal address would be quite a complex query for such a simple output of 1 result because it would necessitate joining three tables business_details, addresses and address types.

And then there is the issue of setting a PK for addresses, if address_type were to be removed. I guess auto_increment is the only realistic way.

bazz

r937 · January 11, 2010, 5:50pm

if you don’t like SET, just split that off

it would ~not~ be a joining table

business to business_addresses is one-to-many

business_addresses to business_address_types is also one-to-many

ABC inc
– 123 Main Street
---- biling
---- admin

XYZ corp
– 456 Oak Street
---- billing
– 789 Elm Street
---- admin

IBazz · January 11, 2010, 6:33pm

Thanks Rudy.

Clearly I have become confused/forgetful, since the break for the festive season and manflu. I know they are 1:many relationships and quite where m:m came from I have no idea.

I’ll push myself for a day or two until I get back into all again.

Thanks again.

Bazz

rpkamp · January 11, 2010, 10:41pm

completely offtopic, but why is enum evil?
I was under the impression I could save some serious data storage here by using an enum like ‘Mr.’, ‘Ms.’, ‘Miss.’.

Normally these would take 3 bytes (‘Mr.’ and ‘Ms.’) or 5 bytes (‘Miss.’) (assuming a 1 byte per character charset like ISO-8859-1, for UTF-16 you need to double the number of bytes).

Using the enum, they use 1 bit (for Mr. and Ms.) and 2 bits for Miss. (‘Mr.’ = 0, can be stored in 1 bit, ‘Ms.’ = 1, can be stored in 1 bit, ‘Miss.’ = 2, can be stored in 2 bits).

So, why is it evil?

r937 · January 12, 2010, 12:18am

do a search for “evil enum” and you’ll see

then holler if it doesn’t make sense

rpkamp · January 12, 2010, 2:43pm

That is mostly about using switch() statements in conjunction with C or C#
How I use it is just to save some data in the database and make sure only a valid value can be selected.
Sounds like a whole different thing to me …

r937 · January 12, 2010, 2:56pm

my apologies, your search term should have been “evil enum +mysql”

have a look at this – http://www.sitepoint.com/forums/showthread.php?t=430646

here’s another one – http://www.dbforums.com/mysql/1213882-look-ups-vs-enum.html