I need a schema for fitness class. The booking system needs to store max-number of students it can take, number of students who booked to join the class, students ids, datetime etc. A student table needs to store classes which he/she booked. But this may not need if I store students ids in class tables. I am hoping to get some good i...
I have two sets of data with the same level of grainularity, for example invoice number. Most of the data required is updated daily as we recognize the revenue for previous invoices. However, some of this data is fed through a seperate costing system once a month and is then fed to the data warehouse with additional information. Shoul...
Why shouldn't in a ER Model an association have attributes with a primary key? I know that later, an association is generally mapped to a database table with a primary key formed by the foreign keys of the two (or more) entities connected to this association. But from a pure ER modelling perspective, what is the reason? Is there any? ...
Can we define a string and a number both as a primary Key in table? ...
I am trying to model the following 2 sentences in an ER Diagram: Each company has a set of tanks. It is also known for each tank how filled up it is for each time of day. It is not known if there are specific times of day for which there are logs. It can be once a day, twice, each hour, maybe some days you don't have any info, ...
Hi, I need to drop and recreate a table, which exists to "cache" an expensive view. The view may change and I want to make maintenance as easy as possible, so I want the new table to reflect the latest version of the view. I also want to be able to prevent read errors should a procedure try to access the table while it is in the middl...
Before I describe my problem, I'd like to get a couple things out of the way: I'm an experienced (though not expert) database designer. I believe I have a good grasp of the relational model. I don't have such a firm understanding of the relational model that I know exactly what to do in every situation. I'm still learning. Let's say ...
I've got one model with 3 addresses: pickup, dropoff, and billing. I figure the billing address will usually be either the pickup or drop-off address, so from a UI perspective, I should have a "same as" option. But from a DB perspective, should I save the "same as" field, or should I duplicate the data? ...
I would like to know your thoughts on how to model votes for product reviews. If you have done any online shopping you might have noticed that many product reviews have an "x people out of x found this review helpful." Assuming many customers will review many products, and many customers will vote on many reviews, is it best to have one...
We have 2 tables. One holds measurements, the other one holds timestamps (one for every minute) every measurement holds a FK to a timestamp. We have 8M (million) measurements, and 2M timestamps. We are creating a report database via replication, and my first solution was this: when a new measurement was received via the replication proc...
Hi all, Imagine you have a database table that stores a list of people. You want to establish a relationship between peoples, i.e., person I is friend with person J. I suppose in this case, one needs a second table to store people associations. This table would contain two fields (person1, person2) and each entry corresponds to a one-t...
I have two Models - Users and Restaurants. They can set tweets like status messages and can have replies. i have the following setup and need your help to optimise the design. users(id, name, ......) restaurants(id, name.....) modules(id, name) [this table stores the modules - (1,user) and (2,restaurant) my message table status_mess...
I'm trying to create a drop down select option using PHP, MySQL, and ajadx I have the PHP and ajax pretty much figured out but I'm stumped on how I should organize my tables I couldn't decide so I created it both ways: The first way the tables are: CourseTbl containing (CourseID*, and CourseName) DateTbl, (DateID*, and DatesOffered(en...
Hi Folks, I'm in the middle of designing a SSAS db. I get the theory and the use of this stuff. Here's the thing, I've got a logging database that logs interesting order statuses which I would like to measure time to complete. I've got these tables (not implemented), to measure status times time_dimension user_dimension status_dimens...
Hi, I am really struggling to normalise a Client Meeting form. The details are as follows Client_Name, Client_phone (Normalised) Date, Time, Location, Person_met, location, key_notes, Staff (repeating groups) In 1st NF, I know the Client_Name (given a client_ID key) would be the foreign key in the repeated group, but I don't know w...
Furthering: http://stackoverflow.com/questions/3910250/database-design-for-dynamic-form-field-validation/ How would I model the database when a particular field can have 0 or more validations rules AND that each validation rule is "related" to another rule via AND or OR. For example, say I have field1 that needs to be minimum of 5 char...
Hi all, Assuming a schema structure as such. ----------------------------------------- Stock (ID, Description, blah blah) ----------------------------------------- StockBarcode (ID, StockID, Barcode, Price, blah blah) ----------------------------------------- What is the optimal way of storing units of measure for your stock items? G...
Hi, I'm trying to implement an internal chat system for our admins to use, but I'm not too sure on the database design. I have this (below) so far, but would appreciate a verification and/or recommendation for improvement. Employees { EmployeeId (smallint) // ... } Chat { ChatId (int) Stamp (datetime) // Obsolete, ignor...
Possible Duplicate: Is there ever a time where using a database 1:1 relationship makes sense? For the sake of simplicity, I'll ask the question straight out: should one-to-one relationships in database design be avoided or this acceptable? I know all of the attributes of this "item" can be all hosted in ONE table, but I fee...
Hi, I'm trying to make a application for keeping attendance for a relative's martial arts studio. I've tried looking around for some similar examples, but I couldn't find any specific or clear enough ones for this kind of application. At the moment, I am using two tables, one for keeping student information, students(id, first_name, la...