one table or two tables to contain information?

[obuspider]

I need to create a table in a db for historical data. Most of this data is broken up by meal type so I would have the following fields; date,meal type, free, reduced, paid. However, I have two other numbers that are not broken down by meal type. Is it better to create a separate table for these? Any recomendations?

 

[MeanGreen]

You have probably answered your own question by stating that the other two numbers are not related to the meals. If you are looking for true normalization, then you would want a seperate table for the other two entities. However, full normalization is not always the answer. Many times we have to denormalize our tables to improve performance. Depending on how closely related the other two numbers are, you may want to have them in your meals table. There really is no right or wrong answer until we understand the business rules that are applicable.

Hope this helps.

 

[SQLSister]

One of the questions to ask is if you will want to query this data in a consolidated fashion? If you would never or ralely ask for the data together, put it in differnt tables. If you will frequently need to see the data together, it might, as MeanGreen noted, be better to denormalize slightly to improve performance.

 

[obuspider]

MeanGreen,

Thanks for the input. I think it would help if I could put them in the same table. Is the following how you would do it though? The last two columns are the ones not broken out by meal type

date meal_type free reduced paid equiv sequiv
1/1/03 breakfast 378 366 400 30 20
1/2/03 breakfast 378 366 400 30 20
1/3/03 breakfast 378 366 400 30 20
1/2/03 NULL 378 366 400 30 20
1/2/03 NULL 378 366 400 30 20

 

[obuspider]

MeanGreen,

Thanks for the input. I think it would help if I could put them in the same table. Is the following how you would do it though? The last two columns are the ones not broken out by meal type

date meal_type free reduced paid equiv sequiv
1/1/03 breakfast 378 366 400 NULL NULL
1/2/03 breakfast 378 366 400 NULL NULL
1/3/03 breakfast 378 366 400 NULL NULL
1/2/03 NULL 378 366 400 30 20
1/2/03 NULL 378 366 400 30 20

 

[MeanGreen]

Actually I would probably create two tables and use a join to get at the data. I notice you have repeating data in the listing. What is going to be your unique identifier for each row within the one or two tables? Can you describe your data a little better to see if we can come up with tables that will get you your results properly each time? Here is my guess of what you have listed:

Table 1 Meals
Create table Meals (dTheDate datetime,
imeal_type integer,
iHow_Paid integer,
iEquiv integer)

Table 2 Meal_Types
Create table Meal_Types (imeal_type integer,
meal_type varchar(25))

Table 3 Paid_Types
Create Table Paid_Types (iHow_Paid integer,
How_Paid varchar(10))

Table 4 Equiv
Create Table Equiv (iEquiv integer,
Equiv integer,
Sequiv integer)

Insert into Meal_types values(1,'Breakfast')
Insert into Meal_types values(2,'Lunch')
Insert into Meal_types values(3,'Dinner')
Insert into Paid_Types values(1,'Free')
Insert into Paid_Types values(2,'Reduced')
Insert into Paid_Types values(3,'Paid')
Insert into Equiv values(1,30,20)
Insert into Meals values ('1/1/2003',1,1,1)
Insert into Meals values ('1/2/2003',1,1,1)
Insert into Meals values ('1/3/2003',1,1,1)

Then to get your data out:

select m.dTheDate,mt.Meal_Type,pt.How_Paid,e.Equiv,e.Sequiv
from Meals m
join Meal_Types mt on m.imeal_type = mt.imeal_type
join Paid_Types pt on m.iHow_Paid = pt.iHow_Paid
join Equiv e on m.iEquiv = e.iEquiv

Again, I am only making a guess on what you really want here. As you can see, I assume you really only want to display 'Free','Reduced', or 'Paid' per row and not all three per row. Just provide a little more information and I will try to help.

Hope this helps.

 

[obuspider]

Hey. Thanks for the help. Not sure what to tell you except that it would be helpful to be able to access it all from the same table. The report I need to generate will look something like follows grouped by date. Hopefully this helps.

b free|B Paid|B red.|l free|l paid|l red.|equiv|sequiv

 

[MeanGreen]

I think you were trying to highlight columns and such with your post, so it is a little confusing. Anyway, can you explain to me a little bit about the data you are extracting for your report. What exactly defines Free verus paid versus reduced? For each transaction, can it only one of these (free, paid, reduced) choices or can it have all three? Will there be more meals then just Breakfast? How does equiv and sequiv related to the meals? The solution I gave you made many assumptions, and I was trying to optimize the input as well as help maintain integrity. I am afraid that with your proposed table, you will find that your reports will not work as you intended.

Hope this helps.

 

[obuspider]

Free, Paid and reduced are just numbers--types of meals. I'd be pulling them all at the same time. The meal types are breakfast and lunch. The equiv and s equiv are related types of meals, but are not broken down into breakfast and lunch. I'm thinking because they are not broken down, I really need to have two separate tables. Does this makes sense?

 

[MeanGreen]

OK. I think I see what you have, although I still do not understand your equiv stuff. I know that I created 4 tables above, but they will help you produce your data appropriately. Try cutting and pasting the data above into a test environment and run the query to see what you get. Also, if you are wanting counts by the meals, then you could use this query:

--For counts by meal_type,how_paid and EQUIV stuff
select m.dTheDate,mt.Meal_Type,pt.How_Paid,e.Equiv,e.Sequiv,count(*)
from Meals m
join Meal_Types mt on m.imeal_type = mt.imeal_type
join Paid_Types pt on m.iHow_Paid = pt.iHow_Paid
join Equiv e on m.iEquiv = e.iEquiv
group by m.dTheDate,mt.Meal_Type,pt.How_Paid,e.Equiv,e.Sequiv
order by m.dTheDate

--For counts by Meal_type
select m.dTheDate,mt.Meal_Type,count(*)
from Meals m
join Meal_Types mt on m.imeal_type = mt.imeal_type
join Paid_Types pt on m.iHow_Paid = pt.iHow_Paid
join Equiv e on m.iEquiv = e.iEquiv
group by m.dTheDate,mt.Meal_Type
order by m.dTheDate

--For counts by meal_type and how_paid
select m.dTheDate,mt.Meal_Type,pt.How_Paid,count(*)
from Meals m
join Meal_Types mt on m.imeal_type = mt.imeal_type
join Paid_Types pt on m.iHow_Paid = pt.iHow_Paid
join Equiv e on m.iEquiv = e.iEquiv
group by m.dTheDate,mt.Meal_Type,pt.How_Paid
order by m.dTheDate

--This will produce a crosstab of the how_paid
select m.dTheDate,mt.Meal_Type,sum(case when How_paid = 'Free' then 1 else 0 end) 'Free'
,sum(case when How_paid = 'Reduced' then 1 else 0 end) 'Reduced'
,sum(case when How_paid = 'Paid' then 1 else 0 end) 'Paid'
from Meals m
join Meal_Types mt on m.imeal_type = mt.imeal_type
join Paid_Types pt on m.iHow_Paid = pt.iHow_Paid
join Equiv e on m.iEquiv = e.iEquiv
group by m.dTheDate,mt.Meal_Type,pt.How_Paid
order by m.dTheDate


Hope this helps.

 

[obuspider]

Thanks for the help. I'm working on it and will let you know.