Advice - Table structure 1

[ZOR]

I have two tables in a one to many relationship. The user has now asked if a compilation set of records be made. He wants to copy a parent table record into a new table, and be able to select current linked many records into a new child table. Basically if table 1 contained the details of a singer, and the other table contained artist tracks, he wants to generate a compilation database record. In the new child table he may want to add other track records from another current primary record of that artist. I am looking at copying the master parent record into a new linked table set, however want any ideas as it seems a waste of storage. If I should create a juction table how would I do it. Thanks

 

[fneily]

What? You already have that, don't you? The singer is in the Parent table, the tracks in the Child table. So why can't you just create a form with a dropdown that shows all the singers and when the user selects a name, then the form displays all the tracks? And if the user wants to add a new one, they can.
Or maybe I don't understand some missing info.
Anyway, what they're asking for is against Normalization!

 

[ZOR]

Thanks, I will go again. A videotape barcode number is stored in a parent table together with other fields. The child table contains individual videclips data.

The user wants to create anaother pair of tables. In the new parent table some of the data from the current parent table needs to be imported, possibly just copied over and amended where needed. The new child table will get some of the current childs records copied over, together with selections from other child table records. He wants this new tableset to exist as a varient pair. In other words its a compilation record which is stored. Sorry if this is still confusing. I am trying to avoid data duplication if possible but stuck on how I can avoid it. Regards

 

[danvlas]

You need an 'Albums' table
AlbumID PK
SingerID FK to SingerTracks
TrackID FK to SingerTracks

In SingerTracks you will have TrackID primary key, then a unique Index on TrackID and SingerID

That would allow you to make multiple compilations on the same artist.
If you want compilations on various artists, SingerID will be FK to Artists and the unique index must be deleted.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ZOR]

Many thanks. Am I correct, the Albums table is a juction table?

I am trying to translate it into what I have.

TXMASTERS TABLE
ID1 PK

TXCLIPS TABLE
ID1 FK
ID2 PK

Thanks

 

[danvlas]

Yes, it is a junction table.

You will need both ID1 and ID2 fields, related respectively, and you have your structure.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ZOR]

Thanks. I have now linked up the junction table, and I can see (I think) the workings of it, however its a mystery ll how I use it, ie where/how does the data go in to create a compilation record of records. I presume the junction table does not have anymore fields as its just in a joining function. Sorry to be dumb, never been down this road. Thanks

 

[danvlas]

Make a continuous form based on the junction table
Place it as a subform in the Artists single form
Link child fields to master fields (ID1 to ID1 I assume)
Then add new records by filling only ID2 in the subform.
A combo box might work well for data entry.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ZOR]

The pennies dropped now. Thanks for all your help, have a good weekend. Regards

 

[ZOR]

I have had to cheat, unless there is a way round it. I had to copy the table structure of my original parent table into the junction table as a variant copy is required of this data. Any way round it, thanks

 

[danvlas]

If there is anything different in the 'compilation' from the original (data may suffer changes), you don't have a choice. It can't be called redundance: the original data is proposed by the program, but the outcome could be different.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ZOR]

Thanks again. It's doing whats wanted so thats the main thing. Regards