1

I am new to this so please guide me if I may have missed standard practice in asking question.

I have two tables. These are used together and practically there is no use of them individually. I have attached sample structure in the images here.

Table that contains queries and parameters needed as input to those queries.

Table that contains positional and format of the data for each field

First image contains the queries that will provide value of the field when executed. Second image contains the table that provides positional and format info which will be used when the label is printed.

In short, first table gets the value and second gives the info about where to put the value on page when printed.

PROBLEM: The first table has around 350 records. Meaning 350 different fields whose value will needed to be fetched every time someone hits "print".

Second table has over 100K records (350 fields x 300 different label templates). So there is a redundancy in second table. I am storing same type of info for same fields again and again for 300 times.

Is there any way I can avoid this? I am hesitant to create too many tables and I also want to avoid this type of redundancy.

closed as unclear what you're asking by MDCCL, mustaccio, Colin 't Hart, Md Haidar Ali Khan, McNets Oct 1 '18 at 8:06

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

For Clarification

If I understand the question correctly then "...you think there is a redundancy in table 2..." which contains the positional information of each element when printing a label?

So each Label_ID has up to 350 possible elements (Field_ID) which are printed according to the information in Table 2?

Answering Your Question

I personally don't actually see any redundancy, because each element can be positioned and formatted differently from label to label.

e.g. Field_ID = 301 with Label_ID = 1 has different formatting and positioning parameters than 301 and 2.

This is possibly true for each Field_ID in combination with Label_ID, which leads me to the conclusion that there isn't much room for any additional normalisation.

Squeezing The Lemon

You could find similar X,Y pairings/values and store those in a separate Positions table and then link them back with a Position_ID in your second table. But depending on the data you might no be saving a lot of space.

  • Thank you for your response. I was trying to make it more normalized but didn't find any possible solution. I will stick with current approach. your "squeezing the lemon" approach is good but i looking at other parameters of my data, I believe it will not help much. Once again, thanks for the reply. – user3421000 Sep 27 '18 at 15:49
0

Use "Normal Forms" to resolve your tables into a precise table which will contain both table data information, there are many Normal Forms, so please read about Normal Forms based on your requirements. Other way is that you can have a foreign key from first table in second table, so that you may not require to store first table data again in second table, while accessing in query u can put condition on that foreign key and access both table data rather than using first table data again in second table. I hope you got it, if not please let me know!!

  • I will look into Normal Forms and try to find which best fits my requirement. – user3421000 Sep 26 '18 at 16:27
  • As for your other suggestion, I think I need to make little clarification. There are N number of templates. Each field is located at different place on each template. Hence if I want to keep the data separated, I will have to create different table for each template which means 300 tables just for printing data(single functionality). So I am trying to avoid that situation. I can work with a couple of tables but not 300 for just one functionality. – user3421000 Sep 26 '18 at 16:33
  • No, i didn't mean creating separate table for all templates, u need not create table for all template, an easiest way is making Field_ID & Label_ID as combined primary key, then you will never find any redundancy, you can use your same table then. But what i prefer is use normalization. It is the best practice while dealing with these kind of table. Though Normalization makes your hands dirty but it is the most suitable way for representing RDB tables. – Prem Acharya Sep 28 '18 at 8:03
0

I assume you have quite a number of fields that do not change their format from label to label, so you could reduce redundancy by adding a table called "template" with the distinct format information and just refer to the id of this table in your second table.

This quick query will give you a hint how much you would save using this concept:

SELECT COUNT(*), X, Y, Bold, Size, Align, Length, Angle
FROM Table2
GROUP BY X, Y, Bold, Size, Align, Length, Angle

On the other hand, I am not sure the payoff would be worth complicating your design: assuming all fields in your table are 4 byte INTEGERS, the whole table is probably less than 4 MB in size. And I don't assume it will grow much larger over time.

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