Creating a Products Table

[paulthepaddy]

i don't remember how i came across the video i found, but i always thought that Linq was lambda :S but now i have been watching a few videos on Linq to SQL classes, i defiantly think this is the way to go for me.


but i would also like some help in creating a few tables that would be similar to a products and category tables but slightly more complicated.

i don't have any products we offer services, also the database doesn't need to contain unit prices and the likes of. but what is throwing me is the relationships between everything

at the moment we offer 2 main services which in turn have a sub service so to speak, which are performed on an area of a car. eg

Sprayless - Compound - bonnet

Paint - Parts - Wing Mirror

do i create 3 tables category - sub category - ??panel??

and how would i go about making the primary keys

should i use a mixture of FK and composite keys

im struggling to word this so if you guys need to ask questions it would possibly be easier to answer a question that trying to word this all out with it makeing sense :S

 

[jmcilhinney]

LINQ is not Lambda but Lambda expressions are used in LINQ. LINQ is an overarching technology but you need to implement a LINQ provider to actually use it against a data source. All LINQ providers expose the same basic interface and that is what we generally know as LINQ but, under the hood, the common methods do different things depending on the data source.

Probably the most commonly used provider is LINQ to Objects, which is what you use on arrays and collections. LINQ to SQL is a LINQ provider specifically for working with SQL Server databases. It is an alternative to using ADO.NET directly yourself but, under the hood, it uses ADO.NET itself, e.g. it uses SqlConnection objects to connect to the database, converts your LINQ queries to SQL code and executes them using SqlCommand objects. I'd actually recommend using the Entity Framework rather than LINQ to SQL. There are numerous similarities but a number of differences too. EF has its own LINQ provider (LINQ to Entities) so you query your data in much the same way but EF also has many more features and supports numerous other data sources.

As for the database design, it's hard to say for sure but it sounds like you probably should have those three tables. The Panel table would have a PanelId column as an identity and primary key, the Service table would have a ServiceId column likewise and the SubService table should have a SubServiceId column likewise. The SubService column should also have a ServiceId column that is a foreign key from the Service table. When you do a job, you would then create a record in another table and record the SubServiceId and PanelId as foreign keys from the appropriate tables. There's no need to record the ServiceId because you can determine that via the SubService table.

 

[paulthepaddy]

Hi JMC,
Thanks for your reply very informative

I had a quick 'look over' a tutorial on the Entity Framework & i have a few questions if you wouldn't mind answering them.

1) Would their be much difference in using EF Over Linq to SQL, does it perform better, and is it easier use & bind the data. The main reason I ask is because I have already started converting everything to Linq to SQL, and am just looking to know is it really going to make a big enough diffrence to start changing it again. Ff you think it is worth it then I'll do it.

2) I did notice in the tutorial I was looking at, he was using stored procedures for everything he was doing. Would stored procedures work better with EF than Linq to SQL or should I use stored procedures no matter which Linq to * I use, that's one of the reasons I really liked the Linq, because it was less hassle than having to fully code the query's.

3) This is more an SQL question but, I am also looking to manage the bills as such. the cars get an Order Number added to them and the Order Number has the work done to the cars. what i was thinking of trying to do was to have the Cars table have a Amount Total, Amount Paid & Amount Due, i haven't started any of this as I'm still messing around with the 'Work Done' parts but I thought the best way for me to do this would be to have the columns read only on the application side and use(i Think) triggers to deal with the maths side of dealing with the total paid and due, what would your thoughts on that be?

Thanks Again
Paul Longden

 

[jmcilhinney]

1. The reason that I suggest using EF is because it is more functional that LINQ to SQL and is also receiving a lot more improvement effort from Microsoft. There's probably not much difference to you between the two right now but, in the future, you can get a lot more out of EF. I only use LINQ to SQL for Windows Phone development because it's basically built in.

2. You can use stored procedures or not either way. Personally, I don't use stored procedures at all and I'd recommend that you don't either. There are pros and cons either way but I'm an application developer and I believe that business logic belongs in the application, not the database.

3. It's hard to say for sure without specific details of the schema and business logic. Again though, using triggers in that way is putting business logic into the database, which I would tend to avoid.