I ‘m designing a data base with SQL Server 2000, and I ‘m an inexperienced with this . I would like to know whether the structure that I have now is efficient, and if I could improve it . The database is for a website that allows you to insert an offer of a car of second hand. Here is a piece of structure of “offers” database:
The tables 1, 2, 3, 4 and 5 there are linked throughout the key field “Offer_num” like a sequence (I linked table 1 with table 2, table 2 with 3, table 3 with 4 and table 4 with 5). The type of data of the “Offer_num” are “Identity” in all tables, and table 2 are linked with table 2-a, 2-b and 2-c.
When it makes the data base of clients, I must connect all the tables by the key field "Number _of _client"? like in this example?
Not knowing the meaning of some of the data, this could be a bit dangerous for me to comment on. Let me just put on my asbestos suit, and take an educated guess or two...
Normally, the rule of thumb that I use is to figure out what "objects" I am trying to model in the database. In this case, it appears to be Offers, Cars, and possibly Clients. I generally try to make a table for each kind of object I am modelling, so I would have a table of Cars, and a table of Offers.
The objects can have several "Attributes" (color of a car, amount of an offer, etc.). Some or all of these can be made into tables of thier own. There is not really a hard and fast rule for this, so this is where the holy wars begin. If you have very few values for your attributes for a large population of objects, (and especially if you want to report on all "red" cars), then I would go ahead and give those attributes their own tables. These attributes are then mapped to cars by ids in the Cars table. This approach gives you much more control over the attribute values that are entered, which is important for some reporting. Also, if you are giving folks the ability to search this database, you want to give them a nice set base of values to look for. I would dread searching for a car in the local newspaper in a programatic way. How many ways are there to spell "Volkswagon" when you are paying by the word, anyway? But I digress.
If you have some attributes that are absolutely inseparable from an object (such as offer amount), then I would put those attributes on the Offers table as columns.
Now the fun really begins, because you can have many Offers on a single Car, with just two tables representing Cars and Offers. This is where you have to use a Mapping table (Car_ID, Offer_ID) to resolve all offers on a car. If you look in the pubs database, you can see this in the titleauthor table in the pubs database, where many titles can be produced by one author. Coincidentally, several authors can contribute on one title, and this construct still comes out fairly clean.
All of this does not really help the original question, but hopefully this will give you a little insight into the workings of database design.
Now for the disclaimers: All of these points raised are my own not-so-humble backward opinion, and should be taken as such rather than the gospel truth some folks may assume it is. There are many ways to skin a cat, but the cat skin market has pretty much bottomed out, so don't bother the cat.
Thanks for your explanation of as you design a data base and the main elements that takings in consideration. Yes, all of this does really help the original question, and it clarify many doubts to me that nonwise nor like requesting them, thank you very much Mcrowley.