none
suggestions to build a data base RRS feed

  • Question

  • hi msdn,

    i making an application such like which print the invoice and this invoice is a bill of cars and i need help to set up a data base i have already make the data base but between in it i was confused with some problem (because i'm new to vb and trying to learning it)

    the problem that i'm facing is that

    • like i told you that this is car invoice and i want that user add the car info in the db and like he/she register the car in its company and when user sell it the db says that it is sell any help to improve the following

    this is my db 

    TABLE:-

            l  BRAND  l   NAME   l  CHASSIS NO.  l YEAR  l  PRICE    l

            l TOYOTA l ACCORD l CL7-3203013  l  2011 l $10000  l

    like this 

    THE USER ADD THE CAR Info in it and now how to set that when the car is sell than the car will be shown as sell

    any help please

    •  and how can i add pictures to above example
    and i'm using SQL server
    Saturday, December 10, 2011 3:04 PM

Answers

  • Hi Chohan550,

    Could you describe more detail about your business requirements? Designing a database requires an understanding of both the business functions you want to model and the database concepts and features used to represent those business functions.

    Please note the data integrity guarantees the quality of the data in the database.  More information, please refer to Data Integrity: http://msdn.microsoft.com/en-us/library/ms184276(v=SQL.90).aspx

    So could you please post the Database Diagrams?

     


    Regards, Amber zhang

    Friday, December 30, 2011 8:21 AM
    Answerer
  • Assuming the cars table is the inventory of cars, you can add an extra column called Qty that will tell us how many of each type cars you have. When you add a car to invoice and sell it, you can de-crement that number by 1 using a trigger on Transactions (or Invoices) table.

    Every time you try to add a record to Invoice (or Transactions) table you first need to check that Qty field to see if you have cars of this type available in your inventory.

    All this work can be done by using a trigger on Transactions table. Alternatively, you can use one stored procedure to sold cars and plug the logic above into this stored procedure.

    If you have only one car of each type, then you can use similar logic. Every time you're about to create a new record in the Transactions (Invoices table) you need to check if that car is already in the Invoices (Transactions) table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, January 1, 2012 5:59 PM
    Moderator

All replies

  • To indicate a date sold, you can add a column to your table to hold the date in question. Look into the datatypes available in SQL Server, to find the best type.

    http://msdn.microsoft.com/en-us/library/ms187752.aspx

    Similarly, for the image you want to add, look into a column to hold a link to the image (potentially stored on a server external to the database, or a column to store the image itself. Again, the link above shows you available datatypes:

    http://msdn.microsoft.com/en-us/library/ms174409.aspx

    You do not indicate how much data your database will hold, but if there will be a large number of rows, your data is not properly normalized. What is the primary key in your table? Normally, when storing data on an individual vehicle, the VIN is used to uniquely identify that vehicle. Is that what you mean by chassis number?

    Saturday, December 10, 2011 4:20 PM
  • SORRY SORRY I forgot to tell u all about my data base

    • i have two tables in my database 
    1. costumer table

     l costumer ID l name l     contact     l

     l     379736    l John  l email or mob l

          2.  invoice table

     l ref no. l  BRAND  l   NAME   l  CHASSIS NO.  l YEAR  l  PRICE    l

     l 74794  l TOYOTA l  ACCORD l  CL7-3203013  l  2011  l $10000  l

    costumer table is linked with invoice table  and  every invoice has different ref no.(reference no - as identity) 

    now i want that first the user add the car into the data base( when user own it) and when going to  sell it the user  get to the invoice form and start generating invoice and in the dgv enter the car chassis the it automatically add the info in it and generates the invoice,

    • now the problem is i am new to vb and learning it, 
    1. i am very successful in generating such data base and such things that i want to do have but now i am facing one problem that how to make such like when user enter the chassis no if already sold then db say it sold and add another chassis no. , so like i want that the data i insert is only used one time and become a history
    2. and  now one more problem that how to set up a form that shows the list of total number of cars sold with specific costumers
    thanks alot...
    Sunday, December 11, 2011 7:43 AM
  • Looks like your question is more related to VB /.NET forums rather than to SQL Server..

    In order to get a sum of sold cars you need to have a customerID column within an Invoice table....

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, December 11, 2011 8:05 AM
    Moderator
    • i already have that see above in costumer table
    And what about my first  question that how set up such db that the item(like car) add to it, can only one time be used for invoice and such report to so all the car sold till that time.

    please help

    thanks allot

    Monday, December 12, 2011 12:49 PM
  • http://www.databaseanswers.org/data_models/car_sales/index.htm

    http://www.databaseanswers.com/data_models/index.htm ------ examples database design


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, December 12, 2011 12:54 PM
    Moderator
  • one table is the one u stated
    TABLE model:-

    BRAND NAME CHASSIS NO YEAR PRICE

    TOYOTA ACCORD CL7-3203013 2011 $10000

    other table maybe the
    sale table that may capture sale data as to at what price which model was sold etc. and we can join the two

    Table sale:
    BRAND NAME CHASSIS NO SALEPRICE
    TOYOTA ACCORD CL7-3203013 $8000

    we may join on CHASSIS NO which may be common to both the tables and in the where condition we may put the sale price being non null.

    select * from model a inner join sale b on a.[chassis no]=b.[chassis no] where saleprice <> Null

    Database consultant NY, USA - Intelcs.com
    Monday, December 19, 2011 4:30 PM
  • sorry for replying late but,

    thanks DBBlogger for such reply, ti seems me helpful but iam not so good at building data base, that why i concern this msdn forum so now can u please explain it more and easily..like how many table i have to make, those this example work please reply

    like ;-

      one table is this 

    BRAND NAME CHASSIS NO YEAR PRICE

    TOYOTA ACCORD CL7-3203013 2011 $10000 

     

    And now i should make another table like this or not 

     'CHASSIS NO'   SOLD  'SOLD PRICE'

     CL7-3203013  YES $111111 

     

    And now 'Chassis No' column is link with first one 'Chassis No' column, is this ok! and one more problem that how these both column  will be shown in one dvg

    please help

    thanks allot!

    Monday, December 26, 2011 2:53 PM
  • please rply im waiting
    thankyou 
    Wednesday, December 28, 2011 11:00 AM
  • hey please reply!

     

    Friday, December 30, 2011 6:11 AM
  • Hi Chohan550,

    Could you describe more detail about your business requirements? Designing a database requires an understanding of both the business functions you want to model and the database concepts and features used to represent those business functions.

    Please note the data integrity guarantees the quality of the data in the database.  More information, please refer to Data Integrity: http://msdn.microsoft.com/en-us/library/ms184276(v=SQL.90).aspx

    So could you please post the Database Diagrams?

     


    Regards, Amber zhang

    Friday, December 30, 2011 8:21 AM
    Answerer
  • first my detail doesn't concert to the forum or my question and second that i described my situation to you already,

    • like, iam building an application which print an invoice through a database and i was very successful in that.( this is an car invoice) now i want that like i add a car into the data base (and the chassis no is the identity key) and when it is sell once the same chassis can't be used in invoice like every thing added to it is used only one time this is my question
    above some one said to make different table and i am confused that how to do that

    ok more info of my data base (sorry i don't know how to add the diagram to the forum)

    i have two table
    1. Costumer 
    2. Cars

    Costumer table has the columns like usually for costumer detail and now the second table

     Cars table has 5 columns ;

    1. Chassis no (primary key AND  one problem is that i want to use it as a identity key but the text is " JZX90-6534155 " any suggestion)
    2. Brand (like Honda, Toyota)
    3. Name (like accord, highlander)
    4. year (like 2011)
    5. color (like black, blue)
    now again my question is that  one i add any car into the car is only used one time and when again the same chassis no is used in the dvg it says already used or something like that any help please now and please help me to  make chassis as the identity key
    thanks alot


    and please don't thing iam being rude.....if so sorry!
    but please help 
    Friday, December 30, 2011 12:31 PM
  • hey no rply yet
    Sunday, January 1, 2012 10:33 AM
  • Assuming the cars table is the inventory of cars, you can add an extra column called Qty that will tell us how many of each type cars you have. When you add a car to invoice and sell it, you can de-crement that number by 1 using a trigger on Transactions (or Invoices) table.

    Every time you try to add a record to Invoice (or Transactions) table you first need to check that Qty field to see if you have cars of this type available in your inventory.

    All this work can be done by using a trigger on Transactions table. Alternatively, you can use one stored procedure to sold cars and plug the logic above into this stored procedure.

    If you have only one car of each type, then you can use similar logic. Every time you're about to create a new record in the Transactions (Invoices table) you need to check if that car is already in the Invoices (Transactions) table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, January 1, 2012 5:59 PM
    Moderator
  • thanks that was a good suggestion

    but i have problems still alive

    like can i make a chassis no a identity kry and its not number and second that every car has its own unique no# that is chassis no is like"JZX90-6534155" usually and so on any suggestion

    and and i was very successful in building a data base in which i registered the data of car into it and now the problem is that the car table registers the car but not sell it any suggestion that how can i make data base which is used to sell it
    thanks alot 
     

    Wednesday, January 4, 2012 2:18 PM
  • I am not sure I understand the exact problem. Can you explain in details your requirements again and show what you have and what is not working?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, January 4, 2012 2:31 PM
    Moderator