Welcome Guest, Not a member yet? Register   Sign In
MySQL database structure - one-to-many-many
#1

[eluser]alexmiller[/eluser]
Hi all

I am at the planning stage of a project and want to get my head around the way to structure a specific part of the database.

The database will store a number of tables, but for the purpose of this question I'll simplify it down. I want a table called businesses and a table called users. Both users and businessess can have multiple telephone numbers (eg. mobile, landline, fax). I want to store the telephone numbers in a separate table to prevent repitition, and to allow multiple numbers for each business.

So, here is my problem. Should I create the telephones table as follows:

Code:
owner_id : the ID of the owner (eg. business_id or user_id)
owner_table : the name of the owners table (eg. businesses or users)
telephone_number : obvious really
telephone_type : eg. fax, landline etc.

This seems to make sense to me, but can anyone see a problem with this? Can anyone suggest what this might be called so that I can have a read up on Google. I also want to use the same structure for addresses.

Kind regards

Alex
#2

[eluser]slowgary[/eluser]
Database Normalization? Where's Jedd when ya need him?

So I'm no expert, but I don't get why you'd do it that way. What is the relationship between business and users? I would either just determine the phone numbers you want to collect and do it this way:
Code:
table_businesses
--------------
id
organization
primary contact
address
phone
fax
mobile

table_users
--------------
id
name
address
phone
fax
mobile

OR... I would combine businesses and users and do something like this:
Code:
table_accounts
---------------
id
organization (N/A for non-business accounts)
primary contact
address

table_phones
---------------
account_id
phone_type
phone_number

But it really depends on what you're doing with the data and what the relationships are. This is just my opinion, I'm no expert.
#3

[eluser]alexmiller[/eluser]
Hi Gary

Thanks for your reply. I can't really use your first solution as some places might have three mobiles, others two landlines. To accomodate all this there would be a huge number of empty fields in the database.

The second seems a good solution, but leaves me with the problem that I might later, for example, decide that I want a table for something else - key staff at a business for example. If I use the second example I might have a problem.

I'll have a think about having a table of objects like your suggestion:

Code:
table_objects
-------------

object_id (auto-incremement)
object_type

table_users
-----------
object_id
username

table_businesses
----------------
object_id
business_name

table_telephones
----------------
object_id
telephone_number

table_addresses
---------------
object_id
building_number
street
town

Essentially the object table would be generating a unique identifier for each object in the database.

Any thoughts on the two options? In summary they seem to be to generate primary keys that are unique across all my owner tables (ie. those that can have telephone numbers or addresses) - the second method. Or to have none unique keys and have references to the table name - the first method.
#4

[eluser]srisa[/eluser]
How about this approach
Code:
tbl_users
--------------
id - int auto_increment primary key
username

tbl_business
-------------
id - int auto_increment primary key
businessname

tbl_type
-------------------------------
id - int auto_increment primary key
typename
(Sample data: 1-users, 2-business) unique

tbl_phone
----------------------------------------------
type - references tbl_type(id)
id   - (references tbl_users(id) or tbl_business(id))
phone - phone number
primary(type, id, phone)
#5

[eluser]rogierb[/eluser]
We had the same problem with addresses and other data and this is what we did:
Code:
table telephone
==========================
connector  <= can be string or id 1 for user, 2 for bussiness, 3 for whatever
connector_id <= the id from whatever connector is
sort <= what sort of number, mobile, landline, voip, skype

Basically a similair structure as you proposed. It works really well especially since the connector is a config item like
Code:
$config['connector'] = array ("landline"=>1,
"mobile"=>2);

And we use $connector["landline"] to reference the connector_id.

The same structure we use for all the different addresses, websites etc.
#6

[eluser]alexmiller[/eluser]
Guys, thanks for all your help - this makes sense and I'm going to summarise what I intend to do here for anyone who searches this topic. I am going to go with the route whereby I have a single table for telephones and a single one for addresses. I will then have the following fields in my tables:
Code:
telephone
---------
owner_id  -> the ID number of the entry with this number (it might be a business, a user, or a customer)
owner_table -> name of the table for this owner ('businesses','users' etc)
telephone_number
telephone_type -> ('landline','fax','mobile')
rank -> tinyint allowing me to customise the order in which telephone numbers are displayed
The structure for the address column will be virtually the same.

This allows me to attach telephone numbers and addresses to any objects (businesses, users, contacts, customers, advertisers etc) in my database. Managing telephone numbers will be easy, and can be handled using a single model which might also deal with formatting for display (domestic without +, international including + and country code).




Theme © iAndrew 2016 - Forum software by © MyBB