Currently working a database design for an application, but I am a bit stuck.
I am wondering how your table structure would look like with the following requirements :
There are say 5 different categories of products. Each one has a different set of
a.) attributes : e.g. clothing - weight, material : wine - colour, year : cement - set time, grade
Attributes are usually constant and don't vary, yet the column names of each category will
b.) options : e.g clothing - sizes, colours : wine - bottle or box : cement - bag or bulk, etc
Options are mostly variables or a list thereof, again the column names of each category will be different
There has been considerable discussion of similar design problems on this list, with some suggesting the attributes be in a single table (keyed by category) and others suggesting multiple tables be used - a separate attribute table for each category. Perhaps searching this group for the previous discussions might be helpful.
In this design I have not yet included the "attributes" and "options" aspects.
The issue that I am thinking about here is whether it is really necessary to maintain 4 seperate tables for the details of each of the "stages" that occur? (initialEnquiry,letterOfIntent, Quotation and Order).
The image that I posted above depicts the following requirement :
There are 2 'main' entities namley,
or customers, people who has an intrest in aquiring a product
Companies that have products to sell
When a Buyer is first entered onto the system, we have the necessary information to create a new record in the InitialEnquiry table. This allows us to list and respond to any InitialEnquiries via
e-mail, phone or fax. Should the client be intrested in our offering, he would send us a LetterOfIntent, specifying exactly the details of the products that he intents to buy. From there, a quotation is created, pdf generated and sent to the client. When the client is finally happy with the quotation, an actual Order is placed.
It is necessary to track the path that an order has followed, e.g. InitialEnquiry -> LetterOfIntent -> Quotation -> Order. However, I connected each one of the 4 tables with the Buyer table in order to avoid having SELECT's that span too many tables. The connection point would have been Buyer -> InitialEnquiry, without a link from Buyer -> LetterOfIntent, Buyer -> Quotation and Buyer -> Order.
An agent is one of our employee's. He is assigned to a main category, such as Clothing or Wine. Each agent have their own set of buyers.
The Product table stores the various products that can be purchased by an Buyer. A product can be supplied by more than 1 supplier. I thought it best to have a link between Product and InitialEnquiry, as the InitialEnquiry is a more general enquiry, rather than actually going through the list of SupplierProducts. Once the actual letterOfIntent is sent, then it would be linked to a SupplierProduct as it shows that the potential buyer is "serious" about purchasing a product.
I do realize that it is probably better to start a new thread, but I thought that it might be good to connect this post with my first, as it places the first post in a better context.
I took a few minutes to look over your diagram. It seems to me that in similar situations, folks usually have a "transaction" or "orders" table, where each transaction/order is kept and a transaction ID is assigned. The transaction can indicate different stages (e.g., initial inquiry, letter of intent, shipping date). It might be worth looking at the retail data models at this site http://www.databaseanswers.org/index.htm.
Well thank you for taking the time to look over the diagram. Much appreciated. You do bring up an intresting point in regards to just letting the 'transaction' have different stages. I presume that should the business require the need for the details to be stored at each stage, that I would still require seperate tables. Or perhaps, can then just have a transactionHistoryTable that stores the transactionId & the details of the transaction, allowing the user to review the history, should the need to. What do you think?
I have taken a look at the website you mentioned, very informative. Thou to be honest, there are so many customer, order, etc designs to go through that I haven't really had the time to do so yet.
Glad the website helped. Regarding stages of transaction, once again we are in the one/many attribute/value table dilemma. Some folks would say to go with a separate table for each stage and others would say to use just one table. I would probably lean towards multiple tables for this application.
Anyway, best of luck - these types of situations can be sticky.
Yes, I tend to agree with having them as seperate tables, seeing as they are distinct seperate entities in the business domain, but the central concern that I have is this :
If I have 4 seperate tables, each one would be linked to its own set of <StageName>Product table, as illustrated in the diagram. Then adding an "Option" for each one of the stages together with 2 "Attribute" tables (One for the default attributes of the products in a category, and one for the product attributes from the Supplier) for the category is 6 tables, for just 1 category. If the application grows to say 10 Categories, and each category having different columns, then thats 60 tables just for the "Attributes" and "Options". I am not sure how this would affect the performance of the RDBMS and/or the application.
Ok, with the "Transaction design" I would still have the 2 "Attribute" tables, but then I would only have 1 additional "Option" table, cutting the number of tables in halve. Thou, if I want to store the history of the options, that would add an additional table, bringing the total to 4 tables per Category. So if we have 10 categories, its a 60 table (stage table) vs 40 table (transaction table).
Regarding performance, flat tables may have a speed advantage over multiple tables. However, normalization may have a size advantage. Some suggest emphasizing normalization in data tables and allowing flat tables in query results. Generally, having multiple tables is not a problem - most queries will only involve a subset of these tables. A bad design is certainly a bigger problem than having multiple tables. So, I wouldn't make the number of tables the most important guiding principla.