Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am PHP programmer with basic-intermediate knowledge on databases. I am working on a project where I need to verify a given phone number's presence in India's Do not call database. Following is the structure of the table,

Phone Number | Flag 1 | Flag 2 | Flag 3 | Flag 4 | Flag 5 | Flag 6 | Flag 7

here each Flag represents a category.

I would be provided a phone number and a category, or maybe phone number and multiple categories.

What would be the best way to optimize the table structure? Which will be best to use? Mysql or Oracle?

Should the table structure be normalized? How should the query be designed?

Response time is a key factor for the solution. The database has about 150 Million records.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

That is a very denormalized design, as you have pointed out yourself. Especially if there isn't always a 1 : 7 ratio of Phone Numbers to Flags. Depending on your business data, you could potentially have a vast amount of sparse data.

I'd recommend a thin, normalized structure like this:

create table Flag
(
    pkFlag int identity(1, 1) not null primary key clustered,
    FValue varchar(100) not null
)
go

create table PhoneNumber
(
    pkPhoneNumber int identity(1, 1) not null primary key clustered,
    PnValue varchar(20) not null
)
go

create table PhoneNumberFlag
(
    fkFlag int not null foreign key references Flag(pkFlag),
    fkPhoneNumber int not null foreign key references PhoneNumber(pkPhoneNumber)
)
go

This allows you to create a one-to-many relationship (or possibly a many-to-many relationship, I can't say for sure as you haven't explained the meaning of Flags or any part of the domain).

A simple query to get all flags for a particular phone number would be:

select 
    f.fValue
from PhoneNumber pn
inner join PhoneNumberFlag pnf
on pn.pkPhoneNumber = pnf.fkPhoneNumber
inner join Flag f
on f.pkFlag = pnf.fkFlag
where pn.PnValue = '555-555-5555'

As for the RDBMS you are looking to go with, that is very dependent on MANY factors. What's your environment? What do you already have? MySql is a very different type, as it is open source. Oracle, SQL Server, these are not. I'm not sure about Oracle, but SQL Server has a free edition (SQL Server Express) that you could leverage, but it is limited. RDBMS licensing can get costly very fast. There are too many questions that need answering before a sound recommendation on that front can be made.

share|improve this answer
    
Flags are actually the list on which the phone number is listed. For example A phone number maybe blacklisted on for Banking calls (Flag 1), but maybe open for Education services calls (Flag 7) My application will work on RHEL 5.5 and I have both MySQL and oracle available on it for other purposes, so I have an open choice to make. The server is fairly strong on hardware front, 4 GBs of main memory and a TB of secondary storage. –  PHPFreak Mar 16 '12 at 12:07
    
@PHPFreak After reading your definition of flags, you might benefit from using bit masks on one field. –  Thomas Stringer Mar 16 '12 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.