Database Design - Question

Question

Hi,

I would like to build an application for a little car service station and I have a question how to design some tables.

I have a table called jobs where the mechanics are able to add works, autoparts, information for costumers etc. 

Now I have a question how to design the table for the works, autoparts, information etc.

Maybe I create seperate tables for works, information and autoparts. With a relation from jobs to works, and jobs to information. And a relation from works to autopars.

Or is it better to create one table for example job lines and add works information and autoparts to this table? The problem is that for example the information only need a text field and autoparts need also price etc.

I also want that it is possible to relate a work to a autopart and information, so that the costumer can see which autopart belongs to which work. I think a groupnumber will help with this?

Maybe someone could give me some tip how to do it.

Thanks.

Answer 1

Hi Dragonalw

As I understand your requirement, It will be better if you create separate entity for work. autoparts, information etc. May be the below relationship will help you.

A Customer can have many Job.

A Job can have many works.

A Job can have many Autopart.

A Job can have many Information.

"I also want that it is possible to relate a work to a autopart and information, so that the costumer can see which autopart belongs to which work. I think a groupnumber will help with this?"

I think to solve your above task it will be better if you create a RIA Service where you can apply GroupBy to get the result.

Regards

---

Rashmi Ranjan Panigrahi
www.lightswitchspecial.com

If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
This will help other users to find their answer quickly.

Answer 2

Hi,

thanks for the fast answer.

I do not understand the thing with the ria service. Maybe you can give me a bit more information?

Is it also possible to show all entries in one grid?

Thanks.

Answer 3

Hi

Yes it is possible to show multiple entities data in one Grid.

Here is an nice article demonstrated by Michael Washington regarding How you can combine two table using RIA Service.

Regards

---

Rashmi Ranjan Panigrahi
www.lightswitchspecial.com

If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
This will help other users to find their answer quickly.

Answer 4

Hi,

thanks, so I have to combine 4 entities in one grid. I just thinking about the ID described in the article. With two it is positive and negative, with 4 I do not know how to do it...

And I do not know how to relate for example an part to a work or a costumer information with ria service?

I just designed a little layout how my grid should look like.

In the first line I would like to show a group, for example a brake service. Next Group for example car inspection etc. After this the parts, work, information etc.

Maybe it is just easier to create a table like this:

I just have to group the grid and I have all I need, the thing is that I have many fields without any data.

It is quite difficult for me to decide how to do this, because maybe I will have restrictions later, if I have a bad design now. I am thinking about this since days. But I have 2 weeks left before I really start with this.

Thanks.

Sorry but it do not work to put image files into this thread correct. It still cuts the last line of the second image.

Answer 5

Hi Dragonalw

I don't know what happened to this forum. I can't able to see any image you posted. Its all showing blank.

Try to attach your image using third party website.

Regards

---

Rashmi Ranjan Panigrahi
www.lightswitchspecial.com

If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
This will help other users to find their answer quickly.

Answer 6

Hi,

I just saved the file in mspaint instead in snipping tool and now it is not blank, but the last line of the image is cut off. But I think you can see what I mean.

Thanks.

Answer 7

"And I do not know how to relate for example an part to a work or a costumer information with ria service?"

There is no easy way to do it .

You should create a new entity for your grid first

Then query the data you want and combine them.

Answer 8

Here is an simple example of a wcf ria service that you could follow. http://hgminerva.wordpress.com/2012/06/21/how-to-create-a-vb-wcf-ria-services-for-microsoft-lightswitch-to-aggregate-or-join-tables-at-the-same-time-pass-a-parameter/

---

"HARD WORK BEATS TALENT WHEN TALENT DOESN'T WORK HARD"

HAROLD GLENN MINERVA
http://hgminerva.wordpress.com

If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. This will help other users to find their answer quickly.

Answer 9

If the entities have relationships defined between them, you DON'T need to create a RIA service to display propeties from related tables. You base your screen on the "main" entity, then you can use "Add Other Screen Data" to add the related properties, if you need more properties than you see by default.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 10

I agree with Yann, with what you are asking it is a simple design and you do not need to go into custom RIA services, from what I am thinking this could be a simple no code solution inside lightswitch. (A Basic Job Scheduler with inventory control and the ability to add steps or notes for each job.)

Answer 11

Hi,

I know that I can add related data on the screen.

But if I use the rules of database normalization, I would build a table for costumer information, parts, work.

So I have 4 grids on my screen and I think this is not very useful for a user. It would be better to have one grid with all the information.

So it is better to use another database design or to use ria service or something else to show the data in one grid?

Thanks.

Answer 12

I wasn't suggesting 4 datagrids. You're thinking of "Add Data Item", I was talking about "Other Screen Data" which will allow you to show related properties of the entity that your grid is based on, all on one row.

Normally navigation properties for related data show automatically in the grid row, but if you need to go further down a related entity, you would use the method I originally \suggested.

If that isn't what you want to do, you'll have to explain it more, or show some screen shots.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 13

Needed a simple design to work on, might give this one a go... :)

Answer 14

Hi,

maybe I do not understand it right, but I do not think that adding another screen data is the right thing for me, because it adds the related data in the same row.

Maybe I explain my problem again. My target is to build a screen with a grid like this one:

If a costumer comes with his car there is always the same process. The costumer informs about the problems of the car. This I call the costumer information (Maybe there is also a language problem, because I just translate it...). For example the mechanic should do a brake service or something else. So you need to add work, parts and maybe some additional information. I would like to do this in groups because this will give the user a better overview (I already saw the post about grouping data in the grid, so this will be possible). For example there are 10 to 20 parts in the job table, the user would lose the overview.

I already read some things about database normalization. So I have to build 4 tables related to group (Costumer Information, Parts, Work, Additional Information).

But I do not know if it is possible to build the grid with this database design. So I do not want to add the data in the same row, I want to add the data in the next row. I hope this describes my problem better. Sorry my english is still bad.

Thanks.

Answer 15

Yes, there does indeed seem to be language problems. :-) It would appear that I've misunderstood what you were trying to explain.

If you want to enter your data in the "grouped" way that you show, you may as well use a spreadsheet. You can't enter data that way in LightSwitch, but you can display the entered data like your image.

What we've been trying to explain is how to enter the data. This would be done using the 4 separate entites. To display the "grouped" data from that would indeed need a RIA service.

This article will show you how to get the data out of your tables in a "grouped" (aggregated) form:

How Do I: Display a Chart Built On Aggregated Data (just ignore the chart bit if you don't want that).

It also sounds like Glenn is going to put something together to show you.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 16

Hi,

thanks.

I think the language problems are solved :)

I read the article about Aggregated Data, so it is possible to do the ria service with Visual Basic 2010 Express? 

With the ria service I am only able to display the data? So it is not possible to change something in the grid?

Would be great if Glenn put something together :)

Thanks.

Answer 17

I'm not sure about whether VS Express will allow you to create a RIA service. It may not have the template, but a RIA service is simply a class. If you can reference an assembly in Express, & add a class you should be able to do it, though I'm not sure how that would work with LS. Normally, any code that needs to be written, outside of LS-related code requires VS Pro (or above).

With a RIA service, if you're aggregating data (which is the "grouping" you mention) then that makes the data read-only. RIA services can update data, as long as it's not aggregated.

However, regardless of what Glenn puts together, you won't be able to edit grouped/aggregated data in a grid.

Does that help?

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.

Answer 18

Do not worry about the parts, work and additional items... How about record them as lineitems with an item type of part, work (Labor) and additional notes. Then a Part may have a cost per qty, where the cost item in work would be a labor cost.

You could then use say the line item to have a product code, then when working back you could get your costs by calculating the line items and matching to an inventory system.

Answer 19

Thanks, Glenn.

So I do not use Database normalization. I use another design to have an easier screen design?

I already posted this table in this thread:

So I create the table without the group field, but i am able to build a table group and make a relation to my line items to have groups.

In my grid I show the lineitems related to my job and add the related group field. So I am able to group with the lightswitch grid.

Parts will maybe need more information. Maybe I just create a table part_info with a 1:1 relation to lineitems to store this information?

Will there be a problem with space or is it a bad design, because I always will have nothing in the costumer information quantity and price?

Thanks.

Answer 20

Hi,

I would like to build an application for a little car service station and I have a question how to design some tables.

I have a table called jobs where the mechanics are able to add works, autoparts, information for costumers etc. 

Now I have a question how to design the table for the works, autoparts, information etc.

Maybe I create seperate tables for works, information and autoparts. With a relation from jobs to works, and jobs to information. And a relation from works to autopars.

Or is it better to create one table for example job lines and add works information and autoparts to this table? The problem is that for example the information only need a text field and autoparts need also price etc.

I also want that it is possible to relate a work to a autopart and information, so that the costumer can see which autopart belongs to which work. I think a groupnumber will help with this?

Maybe someone could give me some tip how to do it.

Thanks.

This is how I will design it.  My transaction database would be a trnJob table with trnJobLabor and trnJobParts as its sub tables.  The trnJob table will contain the mstCustomer, document number, date, totalLabor, totalParts, etc.  The trnJobLabor contains the mstLabor, amount of hours, rate, etc., while the trnJobParts contains the mstPart, quantity, price, etc.  I will have three master files: mstCustomer, mstLabor and mstParts. 

Now if you want your user to define a pre-define Job like, Brake Replacement, etc.  Just make a mstJob master file with mstJobLabors and mstJobParts, then this mstJob can me called in our transaction trnJob.

---

"HARD WORK BEATS TALENT WHEN TALENT DOESN'T WORK HARD"

HAROLD GLENN MINERVA
http://hgminerva.wordpress.com

If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. This will help other users to find their answer quickly.

Answer 21

ohMy goodNess, allThose preFixes make it soHard toRead! :-P

I'd hate to be the one maintaining your code, lol.

---

Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands

If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"

By doing this you'll help people find answers faster.