• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Table and Relationship Design

Hi,

I have a database that I need a little help configuring. This table is used to keep track of cable records for telephone numbers. The main table has these fields

Table: Main

Phone Number
Cable
Pair

My problem is that each phone number needs a unique pair on a cable. I don't know how to configure the table or releation ship.

In addtion, what can a set up as far forms where I can add a cable and specify that this cable X amount of pairs.  

Thanks for any help that you can provide.

Henry

 
0
Henrypb
Asked:
Henrypb
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>each phone number needs a unique pair on a cable
Can you explain this in greater detail?
0
 
HenrypbAuthor Commented:
Thanks for the rapid response. Here is an examle of what I am working with.

Phone number 8513030  gets to the customer on cable 1 pair 5.
Phone number 8513001  gets to the customer on cable 1 pair 10.
Phone number 8516000  gets to the customer on cable 4 pair 25

I want to be able to restrict users from assigning the same cable and pair to a phone number.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In that case, in your table just make Phone Number the primary key, and in the forms that allow data entry before you insert/update a record you'll have to run a query to see if there are already any cable-pair combinations that match what your user entered, and if so do not allow the insert/update.

The relationship between cable-pair 'pair' and phone number cannot be modeled solely in table design.

Hope this helps.
-Jim
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bat17Commented:
If you just want to prevent more than 1 combination of Cable/Pair happening then you could join themtogether as a unique index under
View>Index

HTH

Peter
0
 
thenelsonCommented:
Your in luck.  I am familiar with Access and have worked as phone interconnect tech.

The simplest way to do what you want to do is have a table with two fields:
FIELD 1:
flield name: Cable-Pair (or Cable/Pair or however you want to specify it.)
data type: text
key field
format: "00-00"  I would require two digits to provide for proper sorting
you would enter 01-05, 01-10, 04-25 (or 01/05, 01/10, 04/25)  or if you want, you could identify them as 01-blu, etc

FIELD 2:
field name: phone number
data type: text
field size: 12
format: "999-999-9999"
you can make it indexed no duplicates since I doubt you would assign the same phone # to two different line pairs.
0
 
HenrypbAuthor Commented:
Thanks everyone for the replies. I am going to go with thenelson's approach. Thanks again.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now