Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
edi843
Starting Member
12 Posts |
 Posted - 2009-04-17 : 06:29:13
|
| Hi everybody,I have a big decision to take. I am constructing a database where I will have a table for the sales. Each row, of this table, belongs to a specified company and to a specified year. Let say i will have a table where i will store the companies and a table where i will store the years. I have two alternatives :1. In the table of sales, i store the id of company and the id of the year.2. I construct a third table, where i store the id of the year, the id of the company, and a new id called new_id(uniqe in this table). And in the table of sales i store only the newid.Any idea ?!Thanks in advance |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-17 : 08:12:01
|
| If you want to restrict the years that are available for the company then you would have to go with 2. otherwise I would go with 1.Have you thought of making the id of the year the actual year?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
edi843
Starting Member
12 Posts |
Posted - 2009-04-17 : 08:58:43
|
| My situation is that the years of a company are not restricted. So if, it is possible can you explain the reasons for this solution that you give to me.Thanks for the reply |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-17 : 09:23:32
|
| Mainly that your extra table doesn't add anything.It saves a join but in your situation the year is independant of the company whereas your redundant table tries to represent a model where the company and the year is relevant in it's own right.Say you wanted to get the sales for a company - you would need to join to your company_year table then to company.Every time you wanted the company name you would ened to join to both tables.I might actually go further and have a date table which holds every day and the year and financial year, month, quarter ...This is common in data warehousescreate table dim_date(iddteyearmonth_number ,month_namequarter_number ,financial_year ,financial_year_start_date ,financial_year_end_date ,financial_month_number)Using this you can easily create reports on any date periods.Year, quarter, financial year are common.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
edi843
Starting Member
12 Posts |
Posted - 2009-04-17 : 10:41:52
|
| thank you for your reply. I think i get it. |
|
|
|
|
|
|
|
|
Defining foreign keys