Newbie question

[IPO]

Hi,

I was wondering if a Db design guru could provide guidance for a db newbie??

I have to design a database (and front-end app) that contain reference numbers that relate to various groups of additional numbers.

An example

No. 1124 (ref A) contains 30 numbers (ref B) 01, 02, 03 ect up to 30
No. 1101: contains 60 numbers 01, 02, 03 up to 60 ect
No. 1411: contains 70 numbers 01, 02 up to 70
No. 1024: contains 20 numbers
No. 1045: contains 50 numbers
No. 1058: contains 40 numbers
No. 5214: contains 120 numbers
No. 7845: contains 160 numbers

and so on


Ref B numbers can vary and need to be edited as and when needed.

I'm assuming that each second set of numbers (ref B) need to have individual unique identifiers , but how would I go about creating and mapping to (ref A) numbers??

Any pointers appreciated.



Thanks,
Ipo

 

[Trevor G]

IPO well come to the forum,

I would suggest you need to separate tables and have a relationship, the main would apply your first part as the main group reference and the second table would house your additional sets of numbers relating back to the first table.

 

[dportas]

Looks to me like one table with two attributes (RefA and RefB). (RefA,RefB) would be the key as well.

 

[SOS]

Definitely NOT something to have COLUMNS for the numbers but to have RECORDS as dportas explained.

 

[vbaInet]

Hmm... could the enumeration not be handled in code as well since for each record the numbers start from 1 to n?

So as dportas suggested, a table with just two fields but you only enter the max number:

RefA____________RefB
No. 1124_________30
No.1101__________60

Just a thought.

Unless each number in B would be referring to something?

 

[IPO]

First of all please accept my apologies for not replying sooner - hopefully I can get some further assistance!

vbaInet "Unless each number in B would be referring to something?"

The easiest way to explain would be that RefA is a telephone number and RefB relates to 30 extensions connected to the telephone number, each extension will have its own data that need to be recorded and made active/nonactive whenever a user starts/leaves the company - hope this makes a little more sense!

 

[vbaInet]

Well since that column (RefB) isn't just a number but meaningful data then you would need a junction table.

The junction table will be
RefA_FK_________RefB_FK
No. 1124_________1
No. 1124_________2
No. 1124_________3
....
No.1101__________60

You would make both fields Primary Keys.

 

[IPO]

For 2 telephone numbers and with 10 extensions each- am I correct in saying...

tbl1________________Jcttbl_______________________tbl2

TelephonePK_______tbl1FK__tbl2FK__________ExtensionPK___Extension
12345678_________12345678___1_________________1_________1
_________________12345678___2_________________2_________2
.....
Through to________12345678___10________________10________10

-----------------------------------------------------------------------

8765321__________87654321___11________________11_________1
_________________87654321___12________________12_________2
.....
Through to________87654321___20________________20________10

 

[IPO]

One further Q - if this is correct, is it possible to bulk insert data from another source and how would I update the junction table along with both tables?

 

[vbaInet]

If that's the case then you:

1. Create an ID for your junction table, use an Autonumber ID
2. Create an Extensions table
3. Use the ID from your junction table as the FK in the Extentions table

One further Q - if this is correct, is it possible to bulk insert data from another source and how would I update the junction table along with both tables?

Yes it is possible. I wouldn't worry about that now, just concentrate on your tables and when you get to that bit just post a new thread with your question. But before you do, remember to search the forum, this question has most certainly been asked.

Here are some links on the INSERT and UPDATE statements (for when future ref):

http://www.techonthenet.com/sql/update.php
http://www.techonthenet.com/sql/insert.php

 

[The_Doc_Man]

The way I do bulk imports with dependencies is to import my datasource (say, a spreadsheet) as a table with no Prime Key. Trim anything that shouldn't be there, like rows that are headers only, columns that are blank because they provide formatting only, etc. Reduce that to its minimal "real" data format.

Now add some number fields to the imported table so you can assign indexes.

Write a query that says "SELECT DISTINCT x FROM {the import table name}" so you get a unique list of X.

Now build an APPEND query based on the SELECT DISTINCT query, and be SURE that you have an auto-number field in the target table (the one you are populating.)

Now go back and write an UPDATE query on the import table to write the AUTONUMBER prime key of your target table into that new number column in your import table. What that does is it populates the PK in the import table so that subsequent queries will have it. You have just imported data from the thing you are calling REFA.

Here's the next part. Write another query to take the data that you are calling REFB. Build another table with an autonumber and the data for REFB. AND include a number field for REFA.

Write an APPEND query to your REFB table from the import table. When you append data to REFB, include the number field that you got by back-populating the REFA number field in the import table.

When you are done, you'll have a REFA table that has a primary key and the REFA values. You'll have a REFB table that has a primary key, the REFB values, and a foreign key that matches up to data in your REFA table. You have just populated a parent-child table in entirely too many steps. But it is the methodical way to do this sort of thing. Be sure to declare a relationship (one-to-many) between the REFA prime key and that number-column that is the Foreign Key to REFA in the REFB table.

What I left out there is that when you have repeating data that is associated solely with REFA, you include that in the target REFA table and in the APPEND query that you used to populate REFA. When you have unique data that is associatedly with REFB, you include that in the target REFB table and in the APPEND query that you used to populate REFB. This inclusion might require a bit of study, but it is worth it - particularly if you have a very large number of REFA and REFB records.

NOTA BENE: If you have, say, 5 REFA and no more than 20 or 30 REFB, it is cheaper to just type them in by hand. Your description tells me you have more than that. My approach works but is tedious and easy to confuse.

NOTA BENE: My suggestion uses AUTONUMBER for the prime keys. If your REFA number is short enough, it might also be a viable candidate to be a non-synthetic key. This gets into a discussion of meaningful vs. meaningless keys, which is almost a religious discussion with some folks. You do what makes sense to you. For REFB, since the numbers could possibly repeat(?), a meaningful key is going to be harder to define but a synthetic key is trivial.

 

[IPO]

Guys, thanks for all your help so far The Doc Man - I'll work through your post once I get to that stage, cheers.

However, I've another Q... Within a form, is there a way of creating a bulk insert of new Extensions, and then a way tracking of the new extensions?

For example, 1 new telephone number added which is mapped to 100 newly inserted extensions , 20 put to use and 80 left free for future allocation???

 

[IPO]

Can anyone assist, please??

 

[ColinEssex]

May I congratulate you on the excellent title for this thread.

Criticism is not allowed here any more.

Col

 

[Brianwarnock]

May I congratulate you on the excellent title for this thread.

Criticism is not allowed here any more.

Col

Only pointless criticism, if yours was the first post, suggesting that the poster might get a better response if he had a meaningful title, you might even suggest one to help in future postings, then that's fine, but now and in the form it is, its just petty.

Brian

 

[Brianwarnock]

Guys, thanks for all your help so far The Doc Man - I'll work through your post once I get to that stage, cheers.

However, I've another Q... Within a form, is there a way of creating a bulk insert of new Extensions, and then a way tracking of the new extensions?

For example, 1 new telephone number added which is mapped to 100 newly inserted extensions , 20 put to use and 80 left free for future allocation???

New questions especially if on a new topic should start a new thread in the correct forum to gain the best response.

Brian

 

[ColinEssex]

Only pointless criticism, if yours was the first post, suggesting that the poster might get a better response if he had a meaningful title, you might even suggest one to help in future postings, then that's fine, but now and in the form it is, its just petty.

Brian

I don't know what you want me to do. If I advise people that their title is uninformative then that seems wrong, if I do the opposite that is wrong, if I don't answer the question then that is wrong, if I don't post in post number 2 then that is wrong.

You know? I just don't care anymore. Nobody cares, people don't care, carers don't care, polititians don't care.

I'm sick of it. You say "where's the old Col?" The old Col is finished, this is now me and I've had enough.
I hate people, I hate caring, I hate the world.

Now is the time.

See you.

Col

 

[The_Doc_Man]

I suppose you could do what you ask (reserve 100 extensions for each new phone number) but your biggest issue here is that Access databases are normally 'sparse' - i.e. not full of every possible combination. This is a style thing, but it impinges on space, speed, and the "typical" way to determine whether an extension is allocated or free.

In a 'sparse' database, you only allocate entries for what you actually have assigned. You might use a DCount() function for which the criteria are the phone number and the extension number. (Hint - look up DCount in Access Help if you don't know about it.) The count would be either 1 or 0 (allocated or not allocated, respectively).

As to Colin Essex's post about titles, he is right in what he says but less than polite sometimes. When Colin gets on a issue, he's hard to live with but execution isn't allowed these days for the petty crime of nit-picking without a license. Therefore, ignore Colin. The rest of us do when he gets this way.

 

[IPO]

Guys, all comments gratefully received, even Colins! I'll try to be a little clearer with future questions.