Database design for car part site. (relational database)

database-design

I am working on a directory website for the car's part, in this site the shop owner can register themself and then the user can search for the part based on the year, model submodel and type of the engine of the car after he/she searched. the user chooses the specific part for example Door ..

in this case, the user should see the door only for the car which he/she already searched for (specific car search) .. now for the tables I have created are:

1- shop owner table like:

 user_Id, Name, Info, location

a car's part table which is 6 tables is like this:

 Door table :
 user_Id (reference from shop owner id),side, front/rear, color, price, car 
 model, car submodel, car year.
 Fender table:
 user_Id (reference from shop owner id), side, front/rear, color, price, car 
 model, car submodel, car year.

my question is: a
car model, car submodel, car year attribute for all the tables, is there any way to get rid of this redundancy (repeating attribute)? I must use relational database

Best Answer

you should have a table for the brands, another one for the models related to their respective brand, another one for the submodels related to the submodels and in the last table use compound keys with the keys of the tables and the year and create views for queries.

Related Solutions

Sql-server – Examples of SQL transaction procedures for sales tracking or a financial database

Depending on the complexity of the database design you would probably need to use transacations to maintain data consistency between tables as you change data within the system.

Any examples would be very basic as every database schema would be different. This is where having a database or data architect comes in handy for a project.

Database design advice

                                                  +------------ --- ---+
                                                  | Assembly options   |
+------------+------------+----------+------------+---+---+---+ --- +--+
| assembly ▼ | unit cost  | quantity | total cost | 1 | 2 | 3 |     |50|
+------------+------------+----------+------------+---+---+---+ --- +--+
| VSD55      | £10'000    | 2        | £20'000    | 1 | 1 |   |     |  |

If somebody handed that quote to me, my first question would be "What's option 1 for the VSD55?" The answer would be "I don't know." That information isn't on the quote. In the unlikely event that person got to field a second question, that question would be "What does it cost?" Again, the answer would be "I don't know." A very disturbing silence would follow immediately, during which the person who handed me the quote would imagine how much better it might feel to be run over by a train.

Options must be line items on the quote, along with their unit price, quantity, and total price. Options must be named, not numbered. They should appear directly under their parent assembly, too, not scattered all over hell and half of Georgia.

If you want a shot at my money, you'd better make it crystal clear what I'm supposed to be getting for my money.

There's nothing (much) wrong with 50 check boxes on a user-interface form. That makes it easy to pick options. But the UI code should read the checkboxes and insert the right information into normalized tables.

Best Answer

Related Solutions

Sql-server – Examples of SQL transaction procedures for sales tracking or a financial database

Database design advice

Related Question