relational databases

[Violet_82]

Hi guys,
I'm building a small asp.net application about montlhy expenses. I was given a bit of guidance in this thread https://www.daniweb.com/web-development/aspnet/threads/488532/building-monthly-expenses-application about how to organise the database. The situation currently is this. I have 2 tables in the same database, table "expenses" and "expenseCodes". The first contains the following data:
-id (int, primary key)
-Date (date)
-ExpenseTypes (int)
-Cost (decimal)
-Comment (nvarchar)

The second:
-id (int, primary key)
-ExpenseType (nvarchar)

So, the idea is that in the first table the expenseTypes contains int values and those values correspond to the ids that I keep in the second table: for example, say that 1 in "expenses" corresponds to foodTransaction, 2 = billTransaction, 3 = rentTransaction etc, then would it be OK if I set the ExpenseType as a foreign key in the second table (but the primary key in the first table is the ID)? Or should the foreign key be id in the second table?

[pritaeas]

ExpenseType in the first table should be the foreign key.

[Violet_82]

Oh, Ok hang on a second, as I'm not that good with databases. So, you're saying, in the first table keep id as primary key and then expenseType as foreign key. In the second instead, id still the primary key? So no foreign key in the second one?

That's what pritaeas is saying afaik. The expenseType fieldname in the first table could be changed to expenseId as that would be more descriptively correct and make it a foreign key. Keep the second table as is.

Although this seems to complicate things it is in fact a lot leaner and not liable to duplication errors. The issue then is that you may need to use INNER JOINS in your SELECT queries to output the expenseType string with the data from the first table.

SELECT t1.date, t1.cost, t1.comment, t2.expenseType 
    FROM table1 AS t1 
    INNER JOIN table2 AS t2 
        ON t1.expenseId = t2.id

Something like that. MySQL is quite loose and you don't actually have to provide a constraint (foreign key) for the field although it is deemed good practice. From what I understand you need to use a storage engine like InnoDB in order to support foreign keys. MyISAM does not support them. Until recently MyISAM was preferred for many uses due to InnoDB's lack of support for fulltext searching. InnoDB now supports this and is used as the default storage engine. So, take a look at which mysql version you're running as I believe MyISAM was the default a while ago.

[Violet_82]

OK fab, thanks. Flipping visual studio 2012 seems to have changed the way you set primary keys and foreign keys, very counterintuitive, but hey; they want you to update the string as opposed to set it in visual studio http://bit.ly/1xaOLZ5. Cool, it looks like I've done it:
CONSTRAINT [FK_expenses_ToexpenseCode] FOREIGN KEY ([ExpenseId]) REFERENCES [expenseCode]([Id])
and field name changed to ExpenseId.
I have no idea what database I'm using, trying to get that resolved as well on another thread...
Will try your code too, but first need to see if it submits OK then will try to output!

Sorry assumed it was mysql

[Violet_82]

No problem, well I got somewhere now, thanks for your help, I'll open another thread when the time comes, hopefully shortly!

[Taywin]

One thing to remember, you may need to watch out how you name database column. If it is a foreign key ID, you should have the ID at the end of the field. It could be confusing if you have not touched it for a long while especially with big database (many tables).

[Violet_82]

ok thanks